Is there an SQL select qualifier for executing the Skip () and Take () commands

Question

We are trying to make the equivalent of LINQ select.Skip(50).Take(25) . This is for a library that can hit any SQL database. So that...

• Is there a standard SQL select clause that can do this? ( Pretty sure the answer is not .)
• If not, is there a way to do this specifically for Access, DB2, MySql, Oracle, PostgreSQL, and Sql Server? And if so, how for each of these suppliers?

Answer 1

There is no really lightweight LIMIT in DB2 in MySQL, although you can enable MySQL compatibility when you are in DB2 for Linux / Unix / Windows:

 db2set DB2_COMPATIBILITY_VECTOR=MYS db2stop db2start 

Alternatively, you can use the window function ROW_NUMBER () to get something like this:

 SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn ,S.* FROM your_table AS S ) AS A WHERE rn BETWEEN 10 AND 20 

Answer 2

For MySQL use LIMIT. With one argument, the number of rows to return. With two arguments, the number of line skips and the number of rows returned.

See http://dev.mysql.com/doc/refman/5.5/en/select.html for details.