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.

Similar Reads

List the Last 25% Rows in a Result Set

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....

Setting Up the Environment

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 geeksforgeeks as shown below:...

Using LIMIT Clause

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:...

Using Window Function

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....

Conclusion

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....