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

[PrinceToad]

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?

[Nicolas ALBEZA]

The only way i've found is along the lines of :

result r = db.query<Foo>(FooQuery::bar == 42 + " ORDER BY baz LIMIT 10");

But maybe Boris will be able to help you more =p

Post by PrinceToad
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?

[PrinceToad]

This may works for MySQL
but MS SQL Server doesn't support keyword LIMIT, which has only keyword TOP and is just after the position of SELECT .
such as:
select TOP N * from footable



At 2012-04-11 16:17:06,"Nicolas ALBEZA" <***@gmail.com> wrote:
The only way i've found is along the lines of :


result r = db.query<Foo>(FooQuery::bar == 42 + " ORDER BY baz LIMIT 10");


But maybe Boris will be able to help you more =p


Le 11 avril 2012 05:10, PrinceToad <***@126.com> a écrit :
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?






--
ALBEZA

[Nicolas ALBEZA]

Then the only solution i know about is using Native Views:
http://www.codesynthesis.com/products/odb/doc/manual.xhtml#9.5.

But wait for Boris's answer, i never used ODB with a MS SQL DB, so i may
have missed something.

Post by PrinceToad
This may works for MySQL
but MS SQL Server doesn't support keyword LIMIT, which has only keyword
TOP and is just after the position of SELECT .
select TOP N * from footable
result r = db.query<Foo>(FooQuery::bar == 42 + " ORDER BY baz LIMIT 10");
But maybe Boris will be able to help you more =p

Post by PrinceToad
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?

--
ALBEZA "Pause" Nicolas

[Boris Kolpackov]

Hi,

Post by PrinceToad
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