How to use LIMIT Clause In SQL
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.).
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.