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

by

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.

More Related Contents:

  1. SQL: Repeat a result row multiple times, and number the rows
  2. Does mysql have the equivalent of Oracle’s “analytic functions”?
  3. What resources exist for Database performance-tuning? [closed]
  4. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  5. Which DB technology is sued to solve the following search issue? [closed]
  6. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  7. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  8. Storing JSON in database vs. having a new column for each key
  9. How do I restore a dump file from mysqldump?
  10. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  11. Cast int to varchar
  12. MySQL equivalent of DECODE function in Oracle
  13. Why do results from a SQL query not come back in the order I expect?
  14. Import SQL file into mysql
  15. How to skip certain database tables with mysqldump?
  16. “where 1=1” statement [duplicate]
  17. How to set a MySQL row to READ-ONLY?
  18. MySQL: LAST_INSERT_ID() returns 0
  19. Using reserved words in column names
  20. Are SELECT type queries the only type that can be nested?
  21. Calculating a Moving Average MySQL?
  22. how to cast the hexadecimal to varchar(datetime)?
  23. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  24. Postgres LEFT JOIN with WHERE condition
  25. How do you write a case insensitive query for both MySQL and Postgres?
  26. Strings as Primary Keys in MYSQL Database [closed]
  27. MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
  28. What happens when I exhaust a bigint generated key? How to handle it?
  29. Optimistic locking in MySQL
  30. mySQL SELECT upcoming birthdays

Leave a Comment