How to use ROW_NUMBER() to Get the Subset of Rows In SQL
The ROW_NUMBER() function can be used to get the subset of rows from the table using the CTE which can be useful in the case of pagination of the data.
Let’s check the top 2 rankers of every class using the CTE(Common Table Expression) with ROW_NUMBER() function :
WITH topTwoRankers AS
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY sectionName ORDER BY studentMarks DESC) AS rankNumber
FROM studentsSectionWise
)
SELECT *
FROM topTwoRankers
WHERE rankNumber <=2;
Explanation: Here in the query we have used the CTE expression to get the temporary table from the query used above and the we have used the outer SELCT query to get the students whose rankNumber is less than or equal to 2.
Output:
Explanation: In the result we can see we have the top 2 rankers from each section which are the subset of the table obtained from the query shown in the previous example.
SQL Server Row_Number Function With PARTITION BY
The row number function is one of the window functions used for assigning the row number to the rows in each of the partitions in the order in which they were sorted in the partition using the PARTITION clause, PARTITION only the ORDER clause can be used inside the OVER clause in such case the whole table will be considered as one partition. But the ORDER BY clause is mandatory for using the ROW_NUMBER() function since it arranges the rows in the partitions in that logical order and later ROW_NUMBER() function can assign the row number. In each partition, the row number starts from 1.
Syntax:
ROW_NUMBER ( )
OVER ( [ PARTITION BY col_1,col_2… ] ORDER BY col_3,col_4.. ASC or DESC) AS column_name
Components of ROW_NUMBER() function
- PARTITION BY: This is the main sub-clause that partitions the rows into windows and for each row, the values of window functions applied will be calculated.
- ORDER BY: This is used to order the rows in the partition, by default it is the ascending order. Without the ORDER BY clause, the ROW_NUMBER() function doesn’t work.
- Return type: The return type of the ROW_NUMBER() function is BIGINT.
Contact Us