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

ghz 8months ago ⋅ 165 views

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

Answers

Here are approaches to select the nth row from a database table using database-agnostic and native functionalities for the databases you've mentioned:

  1. Database-Agnostic Approach:
    • You can use the OFFSET and FETCH clauses in combination with an ORDER BY clause to select the nth row.
    • This approach is supported by most modern relational database systems, including SQL Server (starting from SQL Server 2012), MySQL (starting from MySQL 8.0), PostgreSQL, and SQLite (starting from SQLite 3.24.0).
SELECT *
FROM Orders
ORDER BY OrderID
OFFSET 999999 ROWS
FETCH NEXT 1 ROW ONLY;
  1. Native Functionality:

  • SQL Server:

    SELECT TOP 1 *
FROM Orders
ORDER BY OrderID
OFFSET 999999 ROWS;

  • MySQL:

    SELECT *
FROM Orders
ORDER BY OrderID
LIMIT 999999, 1;

  • PostgreSQL:

    SELECT *
FROM Orders
ORDER BY OrderID
OFFSET 999999
LIMIT 1;

  • SQLite: SQLite does not have direct support for selecting nth rows. However, you can use a subquery with LIMIT and OFFSET.

    SELECT *
FROM Orders
ORDER BY OrderID
LIMIT 1 OFFSET 999999;

  • Oracle: Oracle doesn't have direct support for selecting nth rows. However, you can use the ROWNUM pseudocolumn.

    SELECT *
FROM (
    SELECT *, ROWNUM AS rn
    FROM Orders
    ORDER BY OrderID
)
WHERE rn = 1000000;

These queries will retrieve the nth row from the Orders table based on the OrderID column ordering. Adjust the OFFSET value as needed to specify the desired row.

#oracle