PL/SQL Query to List the First 50% Rows in a Result Set
Last Updated :
17 Apr, 2024
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 - geeksforgeeksExamples 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 tableExplanation: 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.
Similar Reads
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 p
5 min read
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
5 min read
List the First 50% Rows in a Result Set When working with substantial quantities of data in MySQL, it's often necessary to extract just a portion of the data. For example, during data analysis or when implementing pagination, you might need to list the top 50% of rows from a query's result set. This article will guide you through the step
4 min read
List the Last 5 Rows of a Result Set SQL (Structured Query Language) is a powerful tool for managing databases and performing complex operations on data. A common requirement when working with large datasets is retrieving the last few rows of a result set. This can be especially useful for tracking recent records, sorting data, or anal
4 min read
List the Last 25% Rows in a Result Set Fetching the last 25% of rows from a result set is a common query that provides insights into the most recent trends in data. This technique is particularly useful for quick analytics or reporting, especially when dealing with large datasets. In this article, we will explore how to list the last 25%
5 min read
List the Last 25% Rows in a Result Set in PostgreSQL In PostgreSQL, extracting specific portions of a result set can be achieved using a variety of SQL techniques. One common requirement is to retrieve the last 25% of rows from a result set. This can be useful for various purposes, such as paginating results, performing analyses on a subset of data, o
4 min read