Selecting the Nth Row in an SQL Database Table

1. Introduction

For database administrators, mastering the skill of filtering rows from a database table is crucial for efficient data retrieval. Moreover, selecting the nth row provides flexibility and control in accessing and manipulating data. Although basic, it’s a building block to effectively address diverse data processing, analysis, and reporting needs.

In this tutorial, we’ll discuss the steps for selecting specific rows in an SQL database table, using the Faculty table from the Baeldung University database.

2. Using OFFSET and LIMIT Clause

A typical method to select a specific row is to utilize the OFFSET and LIMIT clauses in SQL queries. Further, this approach enables the retrieval of the nth row or a set of rows using defined criteria.

Let’s start with selecting a few columns from the Faculty table:

SELECT id, name, position FROM Faculty;

This query returns 72 rows:

 id  |      name       |      position       
-----+-----------------+---------------------
   1 | Anubha Gupta    | Professor
   2 | Anubha Gupta    | Associate Professor
   3 | Anubha Gupta    | Assistant Professor
  21 | Peter Pan       | Professor
  22 | Peter Pan       | Associate Professor
  23 | Peter Pan       | Assistant Professor
  33 | Nando de Fretas | Associate Professor
...
 727 | Stove Joseph    | Teaching Assistant
 715 | Ron Parry       | Assistant Professor
 716 | Ron Parry       | Teaching Assistant
 739 | Karen Walter    | Teaching Assistant
 740 | Kira Wass       | Teaching Assistant
 741 | Sophia Ker      | Teaching Assistant
(72 rows)

We can order the results using the ORDER BY clause:

SELECT id, name, position FROM Faculty ORDER BY name;

This SQL query sorts the result set based on the values in the name column:

 id  |      name       |      position       
-----+-----------------+---------------------
 226 | Ajit Singh      | Teaching Assistant
 225 | Ajit Singh      | Assistant Professor
   2 | Anubha Gupta    | Associate Professor
   3 | Anubha Gupta    | Assistant Professor
   1 | Anubha Gupta    | Professor
 111 | AV Subramanium  | Assistant Professor
 621 | Badrinath Ho    | Professor
...
 401 | Vini Joseph     | Professor
 131 | Wlliam Liu      | Professor
 133 | Wlliam Liu      | Assistant Professor
 134 | Wlliam Liu      | Teaching Assistant
 132 | Wlliam Liu      | Associate Professor
(72 rows)

It’s important to add an ORDER BY clause when using OFFSET or LIMIT to define a specific order for the rows in the result set. If ORDER BY isn’t specified, the order of the results might be unknown.

Further, while this approach is effective for targeted data retrieval, it’s essential to consider potential performance implications, especially with large datasets.

3. The OFFSET Clause

The OFFSET clause of SQL determines the number of rows to skip from the beginning of the query results before showing the subsequent rows.

For example, we can skip over the first 5 rows from the result set and return the remaining rows:

SELECT id, name, position FROM Faculty ORDER BY name OFFSET 5;

We can see an output example:

 id  |      name       |      position       
-----+-----------------+---------------------
 111 | AV Subramanium  | Assistant Professor
 621 | Badrinath Ho    | Professor
 672 | Ballu Singh     | Teaching Assistant
 691 | Brandon Ford    | Assistant Professor
 421 | Brandon Fraser  | Assistant Professor
 512 | Casper Jones    | Teaching Assistant
 221 | Cormen Qiu      | Professor
...
 401 | Vini Joseph     | Professor
 131 | Wlliam Liu      | Professor
 133 | Wlliam Liu      | Assistant Professor
 134 | Wlliam Liu      | Teaching Assistant
 132 | Wlliam Liu      | Associate Professor
(67 rows)

Additionally, we can use the OFFSET clause together with the LIMIT clause to display specific rows or exclude the appearance of a given number of records.

4. The LIMIT Clause

The LIMIT clause in SQL helps to specify the number of rows to return from the query results.

Let’s restrict the result set to the first 5 rows using the LIMIT clause:

SELECT id, name, position FROM Faculty ORDER BY name LIMIT 5;

Initially, this query sorts the complete result set based on the values from the name column. Then, it restricts the result set to the top 5 rows:

 id  |     name     |      position       
-----+--------------+---------------------
 226 | Ajit Singh   | Teaching Assistant
 225 | Ajit Singh   | Assistant Professor
   3 | Anubha Gupta | Assistant Professor
   1 | Anubha Gupta | Professor
   2 | Anubha Gupta | Associate Professor
(5 rows)

We can retrieve a specific row based on the index (nth row) via OFFSET and LIMIT in the same query:

SELECT id, name, position FROM Faculty ORDER BY name OFFSET 5 LIMIT 1;

This query results in a single row, i.e., the row six (5 + 1):

 id  |      name      |      position       
-----+----------------+---------------------
 111 | AV Subramanium | Assistant Professor
(1 row)

Similarly, we can adjust the LIMIT value, to retrieve a set of rows starting from any row.

5. Conclusion

In this article, we used the OFFSET and LIMIT clauses in SQL queries to select the nth row from an SQL table.

First, we used the ORDER BY clause to sort the data based on the column value. Then, we used OFFSET and LIMIT to select the nth row of the table.