List the Last 25% Rows in a Result Set

Listing the last 25% of rows is a common query that we generally use to detect the recent trend in our data. Fetching the last 25% of rows will give us a brief insight into the most recent trend in our data. In some of the cases, where the data set is too large, analyzing the last 25% rows will help us in quick analytics or reporting.

In this article, we will learn about How to List the Last 25% Rows in a Result Set in SQL with the various methods and examples in detail.

SQL provides us with various methods through which we can achieve our task. We need to perform some basic Math to get our result set.

We will calculate the total number of rows in the table first and then perform necessary actions accordingly. We will see the two most prominent methods to achieve our desired task:

1. Using LIMIT clause
2. Using Window Function

To understand How to List the Last 25% Rows in a Result Set in SQL  we need a table on which we will perform various operations and queries. Here we will consider a table called w3wiki as shown below:

Create Table:

CREATE TABLE w3wiki(
  id int PRIMARY KEY,
  name varchar(100),
  course varchar(100)
 );

Insert Values:

INSERT INTO w3wiki(id,name,course)
VALUES(1,'Vishu','Python');

INSERT INTO w3wiki(id,name,course)
VALUES(2,'Sumit','Java');

INSERT INTO w3wiki(id,name,course)
VALUES(3,'Vivek','C++');

INSERT INTO w3wiki(id,name,course)
VALUES(4,'Neeraj','Java');

INSERT INTO w3wiki(id,name,course)
VALUES(5,'Aayush','Python');

INSERT INTO w3wiki(id,name,course)
VALUES(6,'Harsh','C++');

INSERT INTO w3wiki(id,name,course)
VALUES(7,'Rahul','Python');

For this example, we’ll use the LIMIT clause with OFFSET. Firstly, we will calculate the total number of rows in our table. Then, we’ll determine 75% of the total number of rows as our starting value. Finally, we’ll find the difference between the total number of rows and 75% of the total number of rows to get the total number of rows in the result set. Here’s a simpler explanation:

Query:

SELECT * 
FROM w3wiki
LIMIT (
SELECT FLOOR(COUNT(*) * 0.75) FROM w3wiki) ,
 (SELECT COUNT(*) - FLOOR(COUNT(*) * 0.75) FROM w3wiki
);

Output:

Explanation: In the above image, we can see that last 25% rows of the result set are displayed here. Our total number of rows are 7. Therefore, last 2 rows are displayed here. In the query, we can see that, we have used subqueries to calculate the total number of rows of the table and for calculating the 75% of the total number of rows. LIMIT (5, 2) will display 2 rows skipping the first 5 rows.

(Specifying Condition)

We can also fetch the last 25% rows of the result set with some condition. In this case, we will consider a result set where all rows have course ‘Python‘.

Query:

SELECT * 
FROM w3wiki Where course = 'Python'
LIMIT (
    SELECT FLOOR(COUNT(*) * 0.75) FROM w3wiki Where course = 'Python'),
    (SELECT COUNT(*) - FLOOR(COUNT(*) * 0.75) FROM w3wiki Where course = 'Python'
);

Output:

Explanation : In the above image, we can clearly see that only last row of the resultant set is displayed. This is because, there are only 3 rows in the resultant set ( rows which contain ‘Python’ in their course column). This means only one row will be displayed (25% 3 = 1 (approx.).

In this approach, we will use one of the ranking window function i.e. ROW_NUMBER() function. This function generally assigns a unique integer id to each row within the partition.

As we know, 75% of 7( total number of rows) is 5. We will display all the records whose row id is grater than our calculated value. This will return us our last 25% rows of our result set.

Query:

SELECT id, name, course
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn,
           COUNT(*) OVER () FROM w3wiki ) 
WHERE rn > (SELECT FLOOR(COUNT(*) * 0.75) FROM w3wiki);

Output:

Explanation: In the above image, we can clearly see that the last 25% rows of the result set are displayed. As ROW_NUMBER() function assigns a default starting value 1 to starting row, 2 for second row and so on. Thus, we have specified a condition to display the rows only if the row number is grater than our calculated value, in this case it is 5. This will eventually gives us our last 25% rows of the result set.

Overall, listing last 25% rows of the result set is a useful technique when we are dealing with some large data sets. It also help us in analyzing the recent trends in the data and in financial sectors, it helps in knowing the recent transactions. We have seen two most prominent methods through which we can easily solve our problems. We have covered the solution using LIMIT clause and ROW_NUMBER() function. Now you can easily write queries related to it and can get the desired output.