Methods to List the Last 5 Rows of a Result Set

Let’s consider a tabled Employee with the following schema.

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Department, Salary)
VALUES 
    (1, 'John', 'Doe', 'Engineering', 60000.00),
    (2, 'Jane', 'Smith', 'Marketing', 55000.00),
    (3, 'Michael', 'Johnson', 'Sales', 62000.00),
    (4, 'Emily', 'Brown', 'Engineering', 63000.00),
    (5, 'Chris', 'Wilson', 'Marketing', 58000.00),
    (6, 'Jessica', 'Lee', 'HR', 54000.00),
    (7, 'David', 'Anderson', 'Finance', 67000.00),
    (8, 'Emma', 'Martinez', 'Sales', 59000.00),
    (9, 'James', 'Taylor', 'HR', 56000.00),
    (10, 'Olivia', 'Hernandez', 'Finance', 65000.00);

Output:

| EmployeeID | FirstName |  LastName  |  Department  |  Salary  |
|------------|-----------|------------|--------------|----------|
|     1      |   John    |    Doe     | Engineering  | 60000.00 |
|     2      |   Jane    |   Smith    |  Marketing   | 55000.00 |
|     3      |  Michael  |  Johnson   |    Sales     | 62000.00 |
|     4      |   Emily   |   Brown    | Engineering  | 63000.00 |
|     5      |   Chris   |  Wilson    |  Marketing   | 58000.00 |
|     6      |  Jessica  |    Lee     |     HR       | 54000.00 |
|     7      |   David   | Anderson   |   Finance    | 67000.00 |
|     8      |   Emma    | Martinez   |    Sales     | 59000.00 |
|     9      |   James   |   Taylor   |     HR       | 56000.00 |
|     10     |  Olivia   | Hernandez  |   Finance    | 65000.00 |

Method 1: Using the ORDER BY Clause with LIMIT or TOP

One straightforward approach to fetching the last few rows of a result set involves sorting the data in descending order and then limiting the number of rows returned. In SQL Server, MySQL, PostgreSQL, and similar databases, we can achieve this using the ORDER BY clause along with LIMIT or TOP.

-- SQL Server, MySQL, PostgreSQL
SELECT *
FROM table_name
ORDER BY column_name DESC
LIMIT 5;

Output:

| EmployeeID | FirstName | LastName |  Department |  Salary  |
|------------|-----------|----------|-------------|----------|
|     10     |  Olivia   | Hernandez|   Finance   | 65000.00 |
|     9      |   James   |  Taylor  |     HR      | 56000.00 |
|     8      |   Emma    | Martinez |    Sales    | 59000.00 |
|     7      |   David   | Anderson |   Finance   | 67000.00 |
|     6      |  Jessica  |   Lee    |     HR      | 54000.00 |

Explanation: This query orders the rows by the specified column in descending order and retrieves only the top 5 rows. Replace table_name and column_name with your actual table and column names, respectively.

Method 2: Using ROW_NUMBER() Function

Another technique would be through the application of the ROW_NUMBER() function to allocate a sequential integer to every row sequentially based on the desired sort. Then we filter the rows with row numbers that match which are five rows in the last.

-- SQL Server
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY EmployeeID DESC) AS row_num
    FROM Employee
) AS subquery
WHERE row_num <= 5;

Output:

| EmployeeID | FirstName | LastName |  Department |  Salary  | row_num |
|------------|-----------|----------|-------------|----------|---------|
|     10     |  Olivia   | Hernandez|   Finance   | 65000.00 |    1    |
|     9      |   James   |  Taylor  |     HR      | 56000.00 |    2    |
|     8      |   Emma    | Martinez |    Sales    | 59000.00 |    3    |
|     7      |   David   | Anderson |   Finance   | 67000.00 |    4    |
|     6      |  Jessica  |   Lee    |     HR      | 54000.00 |    5    |

Explanation: This query creates a subquery where each row is assigned a row number based on the specified column’s descending order. We then select rows where the row number is less than or equal to 5.

Method 3: Utilizing OFFSET and FETCH (SQL:2008 Standard)

The SQL:2008 standard introduced the OFFSET and FETCH clauses, which allow for more precise control over result set pagination. This method is supported in databases like PostgreSQL, SQL Server 2012+, MySQL 8.0+, and others.

SELECT *
FROM Employee
ORDER BY EmployeeID DESC
OFFSET (SELECT COUNT(*) - 5 FROM Employee)
FETCH NEXT 5 ROWS ONLY;

Output:

| EmployeeID | FirstName | LastName |  Department |  Salary  |
|------------|-----------|----------|-------------|----------|
|     10     |  Olivia   | Hernandez|   Finance   | 65000.00 |
|     9      |   James   |  Taylor  |     HR      | 56000.00 |
|     8      |   Emma    | Martinez |    Sales    | 59000.00 |
|     7      |   David   | Anderson |   Finance   | 67000.00 |
|     6      |  Jessica  |   Lee    |     HR      | 54000.00 |

Explanation: This query offsets the result set by the difference between the total row count and 5, avoiding those rows and then fetching the last 5 rows.

SQL (Structured Query Language) is an important Database for which data management is done with ease and speed. Whether you are a database administrator with many experiences or just starting to understand how SQL works, your ability to extract and manipulate the data you need is always very important.

Retrieving the last few rows of a result set is a common task in SQL, and it can be particularly useful for large datasets, or if ordering of the results is important.

In this article We’ll discuss various ways to get the last 5 rows of a result set in SQL, focusing on the methods applicable to different database management systems.