mysql - How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases:

• SQL Server


• MySQL



• PostgreSQL


• SQLite


• Oracle

I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches:

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)

SELECT *
FROM Ordered
WHERE RowNumber = 1000000

Credit for the above SQL: Firoz Ansari's Weblog

Update: See Troels Arvin's answer regarding the SQL standard. Troels, have you got any links we can cite?

Answer

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.