Charles Roper Charles Roper - 9 months ago 64
SQL Question

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

I'm interested in learning some (ideally) database agnostic ways of selecting the *n*th 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)
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?


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

Basically, PostgreSQL and MySQL supports the non-standard:


Oracle, DB2 and MSSQL supports the standard windowing functions:

    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
  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.