[odb-users] How does one select the TOP N rows from a table using ODB?

[Boris Kolpackov]

Hi,

PrinceToad <princetoad at 126.com> writes:

> I'm using SQL Server, and I'd like to select top N rows from a certain
> table. How can I achieve this using ODB?

There are several options (in order or increased difficulty):

1. If you can switch to SQL Server 2012, then you can use the new
   OFFSET/FETCH clauses:

   size_t offset (0);
   size_t count (10);

   query q ((query::last == "Doe") + 
            "OFFSET" + query::_ref (offset) + "ROWS" +
            "FETCH NEXT" + query::_ref (count) + "ROWS ONLY");

   result r (db->query<person> (q));

2. Just count the number of rows you are interested in while iterating
   and then stop. In SQL Server the results are streamed from the
   database as you are iterating (instead of, for example, being
   loaded all at once into the client memory). So this won't me much
   less efficient than the above approach:

   query q (query::last == "Doe");
   result r (db->query<person> (q));

   size_t count (10);
   for (result::iterator i (r.begin ()); 
        i != r.end () && count != 0; 
        ++i, --count)
   {
     ...
   }

   The major limitation of this approach is that there is not way to
   specify offset in a subsequent query.

3. If you have to use the "SELECT TOP N" syntax, then the only option
   is to use a native view and specify the SELECT statement yourself:

   http://www.codesynthesis.com/products/odb/doc/manual.xhtml#9.5

Boris