List the Last 5 Rows of a Result Set in PostgreSQL

PostgreSQL provides several methods to retrieve data from tables. One common approach involves using the SELECT statement with ORDER BY to sort data, with the option to use DESC for descending order. By understanding these basics, we can explore techniques to list the last few rows of a result set in PostgreSQL.

In this article, We will learn about various methods along with the examples and output for better understanding.

Understanding the Basics

• PostgreSQL offers several ways to retrieve data from a table. One common method involves using the SELECT statement coupled with ORDER BY to sort the data in a desired order.
  
• By default, this sorts the data in ascending order, but by adding the DESC keyword, we can arrange it in descending order.

Set Up an Environment

let's consider a sample table named sample_table with 3 columns (id, name, and age) and 8 rows of sample data. Here's the structure of the table:

| id |   name   | age |
|----|----------|-----|
| 1  |  John    | 25  |
| 2  |  Alice   | 30  |
| 3  |  Bob     | 22  |
| 4  |  Emma    | 28  |
| 5  |  Michael | 35  |
| 6  |  Sarah   | 27  |
| 7  |  David   | 40  |
| 8  |  Lily    | 32  |

1. Using ORDER BY and LIMIT Clause

This method uses the ORDER BY clause along with the LIMIT clause to retrieve the last few rows of a result set.

SELECT *
FROM sample_table
ORDER BY id DESC
LIMIT 5;

Output

| id |   name   | age |
|----|----------|-----|
| 8  |  Lily    | 32  |
| 7  |  David   | 40  |
| 6  |  Sarah   | 27  |
| 5  |  Michael | 35  |
| 4  |  Emma    | 28  |

Explanation: This query fetches all rows from the sample_table, orders them based on the id column in descending order, and limits the result set to the last 5 rows.

2. Using OFFSET

This method calculates the offset (number of rows to skip) by subtracting 5 from the total row count of sample_table and then fetches the last 5 rows.

SELECT *
FROM sample_table
ORDER BY id DESC
OFFSET (SELECT COUNT(*) - 5 FROM sample_table)
LIMIT 5;

Output:

| id |   name   | age |
|----|----------|-----|
| 8  |  Lily    | 32  |
| 7  |  David   | 40  |
| 6  |  Sarah   | 27  |
| 5  |  Michael | 35  |
| 4  |  Emma    | 28  |

Explanation: This query calculates the offset (number of rows to skip) by subtracting 5 from the total row count of sample_table. Then, it fetches the last 5 rows.

3. Using Window Functions

This method uses the ROW_NUMBER() window function to assign row numbers to each row based on the id column's descending order. It then filters the rows based on their row numbers to retrieve the last 5 rows.

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY id DESC) AS row_num
    FROM sample_table
) AS sub
WHERE row_num BETWEEN (SELECT COUNT(*) FROM sample_table) - 4 AND (SELECT COUNT(*) FROM sample_table);

Output

| id |   name   | age | row_num |
|----|----------|-----|---------|
| 4  |  Emma    | 28  |    5    |
| 5  |  Michael | 35  |    6    |
| 6  |  Sarah   | 27  |    7    |
| 7  |  David   | 40  |    8    |
| 8  |  Lily    | 32  |    9    |

Explanation: This query assigns row numbers to each row based on the id column's descending order. Then, it filters the rows based on their row numbers to retrieve the last 5 rows.

Conclusion

Overall, Efficiently retrieving data is a crucial aspect of database management and application development. In PostgreSQL, listing the last few rows of a result set can be solved using various techniques, including ORDER BY and LIMIT, OFFSET, and window functions like ROW_NUMBER(). By understanding and using these methods, you can easy your data retrieval processes and enhance the performance of your PostgreSQL queries