ROW_NUMBER, RANK and DENSE_RANK: Simple Examples

09a - ROW_NUMBER, RANK, DENSE_RANK, NTILE  (VERY SIMPLE version)
---------------------------------------------------------------------
No setup needed. This file creates its own small table.

Marks :      90, 80, 80, 70
ROW_NUMBER :  1,  2,  3,  4   -> always 1, 2, 3, 4 ...
RANK       :  1,  2,  2,  4   -> same marks = same rank, then a GAP
DENSE_RANK :  1,  2,  2,  3   -> same marks = same rank, NO gap

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

IF DB_ID('SqlLearningDb') IS NULL
    CREATE DATABASE SqlLearningDb;
GO

USE SqlLearningDb;
GO

Small table: marks of 7 students in 2 classes

DROP TABLE IF EXISTS dbo.ClassMarks;

CREATE TABLE dbo.ClassMarks
(
    StudentName VARCHAR(20),
    ClassName   VARCHAR(5),
    Marks       INT
);

INSERT INTO dbo.ClassMarks (StudentName, ClassName, Marks) VALUES
('Aarav',  'A', 90),
('Diya',   'A', 80),
('Ishaan', 'A', 80),   -- same marks as Diya
('Kiara',  'A', 70),
('Vihaan', 'B', 95),
('Anaya',  'B', 85),
('Myra',   'B', 85);   -- same marks as Anaya
GO

SELECT * FROM dbo.ClassMarks;

(7 rows affected)

ROW_NUMBER: 1, 2, 3, 4 ... (no ties)

SELECT StudentName, Marks,
       ROW_NUMBER() OVER (ORDER BY Marks DESC) AS RowNum
FROM dbo.ClassMarks;

RANK: ties get the same number, next number is skipped

SELECT StudentName, Marks,
       RANK() OVER (ORDER BY Marks DESC) AS RankNo
FROM dbo.ClassMarks;

DENSE_RANK: ties get the same number, nothing is skipped

SELECT StudentName, Marks,
       DENSE_RANK() OVER (ORDER BY Marks DESC) AS DenseRankNo
FROM dbo.ClassMarks;

All three side by side (best way to see the difference)

SELECT StudentName, Marks,
       ROW_NUMBER() OVER (ORDER BY Marks DESC) AS RowNum,
       RANK()       OVER (ORDER BY Marks DESC) AS RankNo,
       DENSE_RANK() OVER (ORDER BY Marks DESC) AS DenseRankNo
FROM dbo.ClassMarks;

PARTITION BY: ranking starts again from 1 in each class

SELECT ClassName, StudentName, Marks,
       RANK() OVER (PARTITION BY ClassName ORDER BY Marks DESC) AS RankInClass
FROM dbo.ClassMarks;

Topper of each class (ROW_NUMBER = 1)

SELECT ClassName, StudentName, Marks
FROM (
        SELECT ClassName, StudentName, Marks,
               ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY Marks DESC) AS rn
        FROM dbo.ClassMarks
     ) AS x
WHERE rn = 1;

Second highest marks (DENSE_RANK = 2)

SELECT StudentName, Marks
FROM (
        SELECT StudentName, Marks,
               DENSE_RANK() OVER (ORDER BY Marks DESC) AS dr
        FROM dbo.ClassMarks
     ) AS x
WHERE dr = 2;

NTILE(2): split students into 2 groups (top half = 1, bottom half = 2)

SELECT StudentName, Marks,
       NTILE(2) OVER (ORDER BY Marks DESC) AS GroupNo
FROM dbo.ClassMarks;

Practice Questions

1. Give every student a number 1, 2, 3 ... ordered by StudentName.
2. Find the student with the LOWEST marks in each class.
3. Split students into 3 groups using NTILE(3).