How to Select Nth Row in Database Table

Often while using databases, we may need to select nth row from a table. Depending on your database, there may or may not be a straight forward way to do this. In this article, we will learn how to select nth row in database table. We will learn how to do this in each of the popular database systems such as MySQL, PostgreSQL, SQL Server and Oracle.

How to Select Nth Row in Database Table

Let us say you have a table students(id, name) that contains name and id of students. We will look at how to get nth row from this table, for popular databases.

MySQL & PostgreSQL

Both PostgreSQL and MySQL provide a very simple way to get nth row from database table using LIMIT and OFFSET keywords.

SELECT...
LIMIT y OFFSET x

So if you only want nth row from table, you can use LIMIT 1 OFFSET n clause.

SELECT * from students
LIMIT 1 OFFSET n

In the above query, MySQL/PostgreSQL will retrieve only 1 row (LIMIT 1) starting from nth row (OFFSET n).

SQL Server & Oracle

The above method is not supported by ANSI standard. Both SQL Server and Oracle support standard windowing function advised to be used to get nth row from table. Here is the syntax to do this.

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber = n

The above query contains nested select query statement. The inner query basically assigns row number for each row using row_number() window function, and stores it in rownumber column. The outer query selects the required row using this row number value. It simply selects the row where row number is equal to n.

Here is the above query to select nth row from students table.

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM students
) AS foo
WHERE rownumber <= n

Please note, starting PostgreSQL 8.4 and SQLite 3.25 windowing functions are supported. In fact, even MySQL 8.0+ supports windowing functions. So this method will also work in those databases, and can be considered to be a standard one which is platform agnostic.

In this article, we have learnt how to select nth row from database table in popular databases such as MySQL, PostgreSQL, SQL Server and Oracle.

How to Select Nth Row in Database Table