In my Ranking Function article, I have briefly described about ROW_NUMBER() function. Let's have a practical example of this. Assume that we need to find out the mediocre students from a group of students data. First create the sample table and data:
CREATE TABLE Students(
name varchar(20)
,marks int
,grade varchar(2)
)
GO
INSERT INTO Students(name, marks, grade)
VALUES('Bob', 95, 'A+')
,('Joe', 50, 'F')
,('Jack', 89, 'A')
,('Ben', 85, 'A')
,('Holy', 93, 'A+')
,('Zill', 85, 'A')
,('Casper', 89, 'A')
,('Dan', 70, 'C')
,('Carrol', 95, 'A+')
,('Stokes', 70, 'C')
,('Archer', 63, 'D')
GO
Using the common table expression CTE, we can easily accomplish our task.
WITH Mediocores (name, marks, grade,RowNumber) As -- creating a CTE.
(
SELECT name, marks, grade
,ROW_NUMBER() OVER (ORDER BY marks DESC) AS RowNumber -- rank the students based on their marks
FROM Students
)
SELECT name, marks, grade, RowNumber FROM Mediocores -- Select data from CTE
WHERE RowNumber between 5 AND 8
Output
The above query will rank the students using ROW_NUMBER() function from 1 to n base on their marks. Then using the CTE select statement, students who have positioned between 5 to 8 are selected.