PL/SQL Query to List the First 50% Rows in a Result Set

Listing the First 50% Rows in a Result Set can be considered as a normal day-to-day problem. When dealing with data analysis-related tasks, especially with large data sets, fetching all the data at once might create a problem. Getting the first 50% of rows can help in performing the initial analysis. Also, when we are dealing with some unfamiliar datasets, fetching some rows might help us understand the dataset.

In this article, we will be discussing how to fetch the First 50% Rows in a Result Set in PL/SQL. by understanding various examples with detailed explanations and so on.

PL/SQL Query to List the First 50% Rows in a Result Set

PL/SQL Query to List the First 50% Rows in a Result Set can be a crucial tool in data analysis, particularly when dealing with large datasets. It allows users to quickly retrieve and analyze a subset of data, providing insights and make easy for further analysis.

To understand PL/SQL Query to List the First 50% Rows in a Result Set we need a table on which we will perform various operations and queries. Here we will consider a collection called geeksforgeeks which contains information such as id, name, and course as Columns.

Table – geeksforgeeks

Examples of Fetching the List of First 50% Rows in a Result Set

In this, we are going to discuss various examples which will deal with the fetching of list of first 50% rows in a result set.

Example 1: Fetching the First 50% Rows of Table

• In this example, we are going to fetch the first 50% rows of our table ‘geeksforgeeks’. We can clearly see that our table has only 7 rows. So 50% of 7 is 3.5, but we consider the ceil value of the decimal that means we will consider 4 rows to return.
• If we want to return floor value i.e. 3 in this case, we can use FLOOR() function in place of CEIL() function. Let’s see the query for more clear understanding.

Query:

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

Output:

Fetching first 50% rows of our table

Explanation: In the above query, we have first calculated the total number of rows of our table, which is 7 in this case. Then we will loop through each rows of our table. Looping through each row with an extra created row which contains the row number of each row (Row number is a unique id provided by SQL, when a row is entered). We will create this pseudo row with the help of ROW_NUMBER() function. Therefore we will check, if the row number is less than (total rows * 0.5), we will display it. We can refer it to the output image for clear understanding of the working of the query.

Example 2: Fetching the First 50% Rows of Table Where Course is ‘Python’

• In this example, we will fetch only the rows where course is ‘Python’. When referring to the main table, we can observe that there are only 4 rows in table where course is ‘Python‘, there id’s are 108,110, 112 and 115. Therefore, 50% of 4 rows is 2.
• That means we will get 2 rows in the output block. Lets refer to the query for more clear understanding.

Query:

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

Output:

first 50% where course in ‘Python’

Explanation: In the above query, we have calculated the total number of rows, as we have done in the previous examples. We have followed the similar kind of approach as we have done in example 1. The difference is that, we have filtered the query to return only those rows where course is ‘Python’. As we have discussed earlier, there are only four rows where course is ‘Python’. Therefore, 50% of those resultant rows means only 2 rows are displayed in output block.

Conclusion

Overall, fetching the first 50% rows of the result set can hold various real life applications. Whether we are dealing with some data analysis related tasks with some large dataset or dealing with large unknown dataset, this will help us a lot in the better understanding of dataset. In short, to analyze large dataset , fetching first 50% rows can help us to perform initial analysis. We have covered an approach which include ROW_NUMBER() function. This function will help us to form a pseudo row which will contain the row of the respective row. This row will help us to compare and fetch only limited 50% of the row. Therefore, we have covered all the basic concepts to achieve our task with some clear and concise examples. We have also provided clear explanation of the working query.