How to use Window Function In SQL
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.
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.