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

In PostgreSQL, accessing specific rows within a database table is a fundamental operation often required for various purposes such as data analysis, pagination, and reporting. The “nth” row refers to the row with a particular position or ranking in the table, where “n” represents the position or ranking number.

This article aims to explore different techniques for selecting the nth row in a PostgreSQL database table, providing insights into each method’s advantages and disadvantages for developers and database administrators.

How to Select the nth row in a PostgreSQL?

Accessing specific rows within a database table is a basic operation in PostgreSQL. The nth row refers to the row with a specific position or ranking in the table, where n is the position or ranking number. Below is the method that helps us to select the nth row in a table as follows:

Using OFFSET and LIMIT
Using ROW_NUMBER()
Using RANK()

Let’s Setup Environment

Before we delve deeper into the queries, let’s create the table courses in gfg database and insert some sample values in the table. The following code creates the Sample Table and inserts the entries in the table.

CREATE DATABASE gfg;
 // Create a table
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100),
    fees DECIMAL(10, 2),
    instructor VARCHAR(100)
);
// Insert some sample data
INSERT INTO courses (course_id,course_name, fees, instructor) 
VALUES 
    (1,'Java', 10000, 'Akhil'),
    (2,'Python', 15000, 'Jacky'),
    (3,'C++', 20000, 'Sourav'),
    (5,'Java',10000,'Raj');

Output:

COURSES

1. Using OFFSET and LIMIT

The OFFSET clause is used to skip a specified number of rows before starting to return rows. The LIMIT clause is used to limit the number of rows returned by a query. The LIMIT and OFFSET clauses can be used together to return a specific range of rows from a result set.

Syntax:

SELECT * FROM table_name
ORDER BY column_name ASC/DESC
LIMIT number_of_rows
OFFSET number_of_rows;

number_of_rows: is the maximum number of rows to return.

Example: Select the 3rd row from the courses table:

Query:

SELECT * FROM courses
OFFSET 2 LIMIT 4;

In the above query, the OFFSET clause skips the first two rows of the result set, and the LIMIT clause restricts the number of rows returned to 4.

Output:

OFFSET AND LIMIT

2. Using ROW_NUMBER()

In PostgreSQL, the ROW_NUMBER() function is a window function that assigns a unique number to each row within a result set.

Syntax:

ROW_NUMBER() OVER ([PARTITION BY <column(s)>] ORDER BY <column(s)>)

ROW_NUMBER(): This is the function that will assign a sequential row number to each row.
OVER(): This clause specifies the window frame, which determines the set of rows used to perform the calculation.
PARTITION BY <column(s)>: This is an optional clause that groups the result set into partitions based on the specified column(s). The row numbers will be sequential within each partition.
ORDER BY <column(s)>: This clause specifies the order in which the rows should be numbered within each partition (or the entire result set if no PARTITION BY is used).

Example: Select the 4th row from the courses table

Query:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER () AS row_num
    FROM courses
) AS numbered_rows
WHERE row_num = 4;

The above query selects the 4th row from the courses table by using a subquery to calculate the row number for each row and then filtering the result to retrieve the row where the row_num is equal to 4.

Output:

ROW_NUMBER()

3. Using the RANK()

In PostgreSQL, the RANK() function is a window function that assigns a ranking value to each row within a result set based on a specified column or expression.

Syntax:

RANK() OVER (
[PARTITION BY <column(s)>]
ORDER BY <column(s)> [ASC|DESC]
)

RANK(): This is the function that will assign a rank to each row.
OVER(): This clause specifies the window frame, which determines the set of rows used to perform the calculation.
PARTITION BY <column(s)>: This is an optional clause that groups the result set into partitions based on the specified column(s). The ranks will be assigned within each partition.
ORDER BY <column(s)> [ASC|DESC]: This clause specifies the order in which the rows should be ranked within each partition (or the entire result set if no PARTITION BY is used). You can also specify the sort order (ascending or descending) for each column

Example: Select the 4th row from the courses table

Query:

SELECT * FROM (
SELECT *, RANK() OVER (ORDER BY fees) AS row_rank
FROM courses
) AS ranked_rows
WHERE row_rank = 4;

The above query selects the 4th ranked row from the courses table based on the fees column, using the RANK() window function to assign a rank to each row, and then filtering the result to retrieve the row where the row_rank is equal to 4.

Output:

RANK()

Conclusion

Overall, selecting specific rows in a database table is an important task for various use cases, such as pagination, filtering, and analyzing data. OFFSET and LIMIT clauses are often used in combination with an ORDER BY clause to ensure that the rows are returned in a specific order. ROW_NUMBER() function is commonly used in scenarios where you need to retrieve a specific row or a range of rows from a result set. The RANK() function is commonly used in scenarios where you need to retrieve the top or bottom N records within each group.

Article Tags :

Databases

PostgreSQL