Is there a SQL select qualifier to perform a Skip() and Take()

Question

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

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

Answer 1

In DB2, there's not really an easy clause like LIMIT in MySQL, although you can enable MySQL compatibility when you're on DB2 for Linux/Unix/Windows:

db2set DB2_COMPATIBILITY_VECTOR=MYS
db2stop
db2start

Alternatively, you can use the ROW_NUMBER() windowing function to get something like that:

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