How Can I Select the nth Row from a SQL Database Table?

Retrieving the nth Row from a SQL Table

This guide demonstrates how to select the nth row from a SQL database table, covering both database-agnostic and database-specific methods.

A Universal Approach

While SQL standards offer row selection functionalities, many database systems implement them differently. A reliable, cross-database method employs a subquery with the ROW_NUMBER() analytic function:

<code class="language-sql">WITH OrderedRows AS (
    SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNum, OrderID, OrderDate
    FROM Orders
)
SELECT *
FROM OrderedRows
WHERE RowNum = n</code>

Replace n with the desired row number, OrderID with your primary key or ordering column, and Orders with your table name.

Database-Specific Techniques

PostgreSQL and MySQL:

These databases utilize the OFFSET and LIMIT clauses (note that this is a non-standard approach):

<code class="language-sql">SELECT *
FROM your_table
LIMIT 1 OFFSET n-1;</code>

Here, n-1 is used because OFFSET starts counting from 0.

Oracle, DB2, and MSSQL:

These systems support standard window functions:

<code class="language-sql">SELECT * FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY key_column ASC) AS rn,
        *
    FROM your_table
) AS numbered_rows
WHERE rn = n;</code>

Replace key_column with your primary key or ordering column, your_table with your table name, and n with the desired row number.

PostgreSQL and SQLite Updates:

PostgreSQL version 8.4 and later, and SQLite version 3.25.0 and later, both support standard window functions, making the universal approach described above fully compatible.

The above is the detailed content of How Can I Select the nth Row from a SQL Database Table?. For more information, please follow other related articles on the PHP Chinese website!