The ROW_NUMBER () function in SQL Server returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
The RANK() function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.
The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.
The NTILE() function in SQL Server return distributes the rows in an ordered partition into a specified number of groups.
Example
CREATE TABLE Students(
Stud_ID INT IDENTITY(1,1),
Stud_Name VARCHAR(100),
Stud_Mark INT
)
INSERT INTO Students(Stud_Name,Stud_Mark)
VALUES(‘a’,60),
(‘b’,94),
(‘c’,70),
(‘d’,63),
(‘e’,60),
(‘f’,60),
(‘g’,94),
(‘h’,47),
(‘i’,70),
(‘j’,60)
SELECT Stud_ID,Stud_Name,Stud_Mark,
ROW_NUMBER() OVER (ORDER By Stud_Mark) as ‘ROW NUMBER’,
RANK() OVER (ORDER By Stud_Mark) as ‘RANK’,
DENSE_RANK() OVER (ORDER By Stud_Mark) as ‘DENSE RANK’,
NTILE(2) OVER (ORDER By Stud_Mark) as ‘NTILE’
FROM Students
