Home How to select the nth row in a SQL database table?
Reply: 0

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

user8361
1#
user8361 Published in September 19, 2018, 7:19 pm

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?

share|improve this question
  • 2
    Yes. Here's a link to information about the ISO SQL standard: troels.arvin.dk/db/rdbms/links/#standards – Troels Arvin Oct 31 '08 at 7:10
  • 6
    Just to point out that by the definition of a relation, rows in a table do not have order, so the Nth row in a table can not be selected. What can be selected is Nth row in a row-set returned by (the rest of) a query, which is what your example and all other answers accomplish. To most this may just be semantics, but it points to the underlying problem of the question. If you do need to return OrderNo N , then introduce an OrderSequenceNo column in the table and generate it from an independent sequence generator upon creating a new order. – Damir Sudarevic Oct 26 '11 at 13:21
  • The SQL standard defines the option offset x fetch first y rows only. Currently supported by (at least) Postgres, Oracle12, DB2. – a_horse_with_no_name Jun 4 '16 at 13:28

29 Answers 29

active oldest votes
up vote 285 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.451051 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO