How to Select the Nth Row in a SQL Database Table?

sql

database-compatibility

sql-best-practices

data-retrieval

byAlex Kataev·Feb 21, 2025

Snag the nth row with LIMIT and OFFSET in databases like MySQL:

SELECT * FROM your_table LIMIT 1 OFFSET n-1; -- "If you ain't first, you're last" - does not apply to SQL.

Use the ROW_NUMBER() window function in a CTE for SQL Server like a boss:

WITH RowAsNumbers AS (
  SELECT *,
  ROW_NUMBER() OVER (ORDER BY YourOrderColumn) AS RowNum  -- because who needs arrays anyway?
  FROM YourTable
)
SELECT * FROM RowAsNumbers WHERE RowNum = n;  -- success is being confidently lost in a table you built.

Replace your_table, YourOrderColumn, and n with your actual table name, order column, and row number. Just remember the ordinal rule: there's no 'i' in 'team', but there is an 'n' in 'nth' 😉

Structuring data for precise selection

Remember, when it comes to SQL precision in retrieving specific rows, ordering your data set is a must. Without the ORDER BY clause, your nth row might as well be a random row, as systems like SQLite and MySQL don't have a "default" row order.

Compatibility across different databases

For instances where you're dabbling with different databases, the method of ROW_NUMBER() is a savior for cross-database compatibility. This SQL function stands tall in SQL Server 2005+, Oracle, DB2, and PostgreSQL 8.4+, and crash-landed on SQLite in version 3.25.0.

Why bother with nth row selection?

Wondering why you'd bother searching for the nth row? Here's why: Web pagination! Pulling up only a chunk of data aligns with built-in "Prev" and "Next" functionality. But, test on your database to ensure you don't turn your user's next click into a suspense thriller.

Play by the rules... or the database

Trying to tailor your query to a specific database? Then be prepared to dig deeper into the idiosyncrasies of SQLite, MySQL, and PostgreSQL style ordering subsets such as OFFSET and FETCH. But remember to count from 0 in PostgreSQL, or you'll be left scratching your head.

Securing your data retrieval

Crystal clear ordering for accurate results

To avoid the nth-row-went-for-a-walk scenario, especially with date/time fields, always use the ORDER BY clause. This crystal clears your query path and ensures the correct nth row shows up during a LIMIT/OFFSET query.

Square peg, square hole

If your application cruises on a specific SQL database, mold your query to fit its capabilities and syntax. To do this in SQL Server, you might use EXCEPT to select the nth row, provided you packed some extra patience.

Nail your data consistency

Remember, the nth row is prone to accommodate data modifications in the table. To keep things consistent, weigh options such as a table lock during selection or resort to transactional SQL commands.

explain-codes / Sql / How to Select the Nth Row in a SQL Database Table?

Linked

Sqlite - ORDER BY RAND()



How to skip the first n rows in sql query



How do I select all the columns from a table, plus additional columns like ROWNUM?



The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions



How to get N rows starting from row M from a sorted table in T-SQL

