ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE() RANKING FUNCTIONS

 CREATE TABLE #tbStudent

(

            StudentId        INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,

            StudentName VARCHAR(50),

            Section            CHAR(1),

            Marks              INT,

);

INSERT INTO #tbStudent

VALUES

('Rohan', 'A',90),

('Priya', 'A',45),

('Aasif', 'A',90),

('Vivek', 'B',58),

('Arjun', 'B',40),

('Sidharth', 'A',100),

('Aalia', 'A',95),

('Kabir', 'B',90),

('Robin', 'B',40),

('Rahul', 'A',30),

('Anju', 'B',32);

SELECT StudentId, StudentName, Section, Marks, ROW_NUMBER() OVER (ORDER BY Marks DESC) AS RowNumber FROM #tbStudent

SELECT StudentId, StudentName, Section, Marks, ROW_NUMBER() OVER (PARTITION BY SECTION ORDER BY Marks DESC) AS RowNumber FROM #tbStudent

SELECT StudentId, StudentName, Section, Marks, RANK() OVER (ORDER BY Marks DESC) AS [Rank] FROM #tbStudent

SELECT StudentId, StudentName, Section, Marks, DENSE_RANK() OVER (ORDER BY Marks DESC) AS DenseRank FROM #tbStudent

SELECT StudentId, StudentName, Section, Marks, NTILE(3) OVER(ORDER BY Marks DESC) AS [NTILE] FROM #tbStudent