PL/SQL Query to List the Last 25% Rows in a Result Set

We sometimes need to fetch some specific rows from our result set. There are many reasons to do so. When we are working on real-time analytics, focusing on recent data is then our need. This helps us in quicker decision-making as it provides us the quicker insight into the latest data. PL/SQL is a procedural extension of SQL. It provides us the flexibility to write our custom scripts. With the help of PL/SQL, we can easily achieve our said task.

In this article, we deep into some real-world case scenarios where a “Last 25% Rows” listing is required. We will cover the easy approach with some great examples along with their respective explanations.

List the Last 25% Rows in a Result Set

PL/SQL, being a procedural extension of SQL, provides us with a straightforward approach to achieving our tasks. We will compute the total number of rows of our table. Then, we will compute its 25% and store it in a variable. Using the ROW_NUMBER() function of SQL. A unique number is assigned to each row by SQL. ROW_NUMBER() function stores that number in itself. We will use this function to display only those rows which are greater than the specified criteria. We will cover some examples listed below:

1. By Using ROW_NUMBER() Without Condition
2. By Using ROW_NUMBER() With Condition

Setup the Environment

To understand “PL/SQL Query to List the Last 25% Rows in a Result Set“, we need a table on which we will perform various operations and queries. Here we will consider a table called geeksforgeeks which contains id, name, and total_score as Columns.

Table – geeksforgeeks

Create Table:

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

Insert Values:

INSERT INTO geeksforgeeks (id, name, total_score) 
VALUES (108, 'Vishu', 150);

INSERT INTO geeksforgeeks (id, name, total_score) 
VALUES (109, 'Ayush', 148);

INSERT INTO geeksforgeeks (id, name, total_score) 
VALUES (110, 'Neeraj', 152);

INSERT INTO geeksforgeeks (id, name, total_score) 
VALUES (111, 'Sumit', 140);

INSERT INTO geeksforgeeks (id, name, total_score) 
VALUES (112, 'Vivek', 132);

INSERT INTO geeksforgeeks (id, name, total_score) 
VALUES (115, 'Harsh', 137);

INSERT INTO geeksforgeeks (id, name, total_score) 
VALUES (116, 'Rahul', 128);

Examples of List the Last 25% Rows in a Result Set

In this, we will discuss various examples related to our topic. We will examples related to real world use cases with their respective explanations.

Example 1: Using ROW_NUMBER() Function Without Specifying any Condition

In this example, we will use the ROW_NUMBER() function to achieve our tasks. This function creates another pseudo column that displays the row number of corresponding rows. We will also use the COUNT() function to count the total number of rows. Let, ‘s take a look at the query for more better understanding.

Query:

DECLARE  
    v_totalRows NUMBER;  
    v_last25 NUMBER;  
BEGIN  
    SELECT COUNT(*) INTO v_totalRows FROM geeksforgeeks;  
    v_last25 := CEIL(v_totalRows * 0.25);  
    FOR i IN (  
        SELECT *
        FROM (  
            SELECT id, name, total_score,  
                   ROW_NUMBER() OVER (ORDER BY id) AS rowNumber  
            FROM geeksforgeeks  
        )  
        WHERE rowNumber > (v_totalRows - v_last25)  
    ) LOOP  
        DBMS_OUTPUT.PUT_LINE('ID: ' || i.id || ' ,Name: ' || i.name || ' ,Total Score: ' || i.total_score);  
    END LOOP;  
END;

Output:

Using ROW_NUMBER() function without Condition

Explanation: In the above query, we first compute the total number of rows of the table with the help of the COUNT() function and store it in a variable name “v_totalRows”. After that, we will loop through each row with its row id. As we have to display only the last 25%, so we will display only rows with row id greater than the difference between the total rows and 25% of rows. This will give us our answer. We can refer to the image for a clear visualization of the result.

Example 2: Using ROW_NUMBER() Function with Condition

In this example, we will display only those rows where the total_score column has a value of more than 135. As we can refer to our main table, there are only 4 rows with total_score more than 135. They are 108, 109, 110, in and cases. As we all know 25% of 4 is 1. Therefore we know that our query will return only one row. Let’s refer to our query for more clear understanding.

Query:

DECLARE  
    v_totalRows NUMBER;  
    v_last25 NUMBER;  
BEGIN  
    SELECT COUNT(*) INTO v_totalRows FROM geeksforgeeks;  
    v_last25 := CEIL(v_totalRows * 0.25);  
    FOR i IN (  
        SELECT *
        FROM (  
            SELECT id, name, total_score,  
                   ROW_NUMBER() OVER (ORDER BY id) AS rowNumber  
            FROM geeksforgeeks  
        )  
        WHERE rowNumber > (v_totalRows - v_last25)  and total_score > 135
    ) LOOP  
        DBMS_OUTPUT.PUT_LINE('ID: ' || i.id || ' ,Name: ' || i.name || ' ,Total Score: ' || i.total_score);  
    END LOOP;  
END;

Output:

Using ROW_NUMBER() function with Condition

Explanation: As we have stated earlier, we will only get one row as a result. The above query is same as the previous one but the difference is that in the WHERE clause, we have specified our desired condition i.e. total score should be greater than 135. You can refer to the image for clearer understanding of the output.

Conclusion

Overall, fetching the last 25% of the rows in the result set can be important when we are working with real-time analytics. We can fetch the latest result, which will give us a much better insight into our result set. We have covered how we can achieve this task in PL/SQL (PL/SQL is a procedural extension of SQL which gives us the flexibility to write our custom scripts). We have covered how we can fetch the last 255 rows with the ROW_NUMBER() function.