PL/SQL Query to List the Last 5 Rows of a Result Set

Fetching or displaying the last five rows is a common task. Whether fetching trending topics or tracking a user's recent activity, fetching the last five rows will help in these situations. These are a few use cases we have discussed, there are many more. Product recommendation is crucial if you are maintaining an e-commerce website.

For this reason, too, we have to fetch recent rows. PL/SQL is a procedural extension of SQL which provides us the flexibility to write or modify our scripts. Through PL/SQL, we can easily achieve our said task. In this article, we will discuss briefly how to fetch the last 5 rows from a result set. We will cover all basic concepts with some clear and concise examples along with their explanations.

Setup the Environment

In this tutorial on “PL/SQL Query to List the Last 5 Rows of a Result Set “, we will use the following table for examples:

Create Table:

CREATE TABLE geeksforgeeks 
( 
    id NUMBER , 
    name VARCHAR2(50), 
    rank NUMBER 
);

Insert Values:

INSERT INTO geeksforgeeks (id, name, rank)
VALUES (108, 'Vishu', 1);

INSERT INTO geeksforgeeks (id, name, rank)
VALUES (109, 'Ayush', 2);

INSERT INTO geeksforgeeks (id, name, rank)
VALUES (110, 'Sumit', 3);

INSERT INTO geeksforgeeks (id, name, rank)
VALUES (111, 'Neeraj', 4);

INSERT INTO geeksforgeeks (id, name, rank)
VALUES (112, 'Vivek', 5);

INSERT INTO geeksforgeeks (id, name, rank)
VALUES (115, 'Harsh', 6);

INSERT INTO geeksforgeeks (id, name, rank)
VALUES (116, 'Rahul', 7);

1. Using a Basic Approach

In this method, we will cover some very basic approaches to achieve our end goal of last 5 rows. We will first calculate the total number rows of our table. If the rows is less than 5, then we will display the error message. Otherwise, we will compute the nth row from where last 5 queries will be displayed (i.e. total number of rows - 5). We will maintain a row count too. If the row count exceeds the nth row of last 5 rows, we will display the row. Lets refer to the query for more clear understanding.

Query:

DECLARE
    v_totalRows NUMBER;
    v_last5 NUMBER := 0;
    v_row_count NUMBER := 0;
BEGIN
    SELECT COUNT(*) INTO v_totalRows FROM geeksforgeeks; 
    v_last5 := v_totalRows - 5; 

    IF v_totalRows < 5 THEN
        DBMS_OUTPUT.PUT_LINE('Error : There are less than 5 rows in the table.');
    ELSE
        FOR i IN (
            SELECT *
            FROM (
                SELECT *
                FROM geeksforgeeks
                ORDER BY id 
            )
        ) LOOP
            IF v_row_count >= v_last5 THEN
                DBMS_OUTPUT.PUT_LINE('ID: ' || i.id || ', Name: ' || i.name || ', Rank: ' || i.rank); 
            END IF;
            v_row_count := v_row_count + 1;
        END LOOP;
    END IF;
END;

Output:

Explanation: In the above query, we have successfully performed error handling. We have calculated total number of rows of our table. With this total number of rows we will subtract 5 which will eventually give us the starting index of the last 5 rows. Now we maintain a row count. If the row count becomes equal or grater than the starting index of last 5 rows, then we will display it. You can refer to the above displayed image for more clear understanding.

2. Using ROW_NUMBER() function

In this method, we will use ROW_NUMBER() function to achieve our task. Likewise in the previous method, we will compute total number of rows of the table. ROW_NUMBER() refers to the special id which is provided by SQL, when a row is inserted. IT usually starts with 1 and increment by 1 for the other succeeding rows.

Query:

DECLARE 
    v_totalRows NUMBER; 
BEGIN 
    SELECT COUNT(*) INTO v_totalRows FROM geeksforgeeks; 
 
    IF v_totalRows < 5 THEN 
        DBMS_OUTPUT.PUT_LINE('Error: There are less than 5 rows in the table.'); 
    ELSE 
        FOR i IN ( 
            SELECT * 
            FROM (   
                SELECT id, name, rank,   
                       ROW_NUMBER() OVER (ORDER BY id) AS rowNumber   
                FROM geeksforgeeks   
            )   
            WHERE rowNumber > v_totalRows - 5 
        ) 
        LOOP 
            DBMS_OUTPUT.PUT_LINE('ID: ' || i.id || ', Name: ' || i.name || ', Rank: ' || i.rank ); 
        END LOOP; 
    END IF; 
END; 

Output:

Explanation: In the above query, we have handled error just as we did in the previous example. We have calculated the total number of rows of our table. If the row number becomes grater than the starting row index, then we will display the row. We have followed the similar approach as we did in the previous example, just replace the manual row count with the ROW_NUMBER() function. You can refer to the image for more clear understanding of the working of the query.

Conclusion

Overall, fetching the list of last 5 rows is basic and common task. We need to fetch last 5 rows if we are dealing with application which tracks user recent activity. We also need this, if we are dealing with an Ecommerce website where product recommendation is very important. We have covered a basic and a approach which includes ROW_NUMBER() function. We have covered all the basic concepts with the clear explanation of the working of the query.