3 Ways to RANK Data in SQL

What are the Ranking Functions in SQL

There are 3 simple functions that you can use to rank data in SQL. If you are in a rush and want to dive right into learning how to use these ranking functions in SQL. Check out the YouTube short:

Here are the ranking functions that will show you how to rank student data based on student test marks. However, you will be able to rank any data after reviewing this tutorial.

1. RANK():
   • Assigns a unique rank to each distinct row.
   • Leaves gaps in the ranking for duplicate rows. For instance, if two students have the same marks and are ranked 2nd, the next student will be ranked 4th (skipping the 3rd rank).
2. DENSE_RANK():
   • Also assigns a unique rank to each distinct row.
   • Does not leave gaps in the ranking for duplicate rows. Using the previous example, if two students have the same marks and are ranked 2nd, the next student will be ranked 3rd (not skipping any rank).
3. ROW_NUMBER():
   • Assigns a unique number to each row based on the order specified.
   • Even if two rows have the same values, they will get different row numbers.

Let’s Take a Look at Some Data to Rank

student_id	name	marks	rank	dense_rank	row_number
110	Chen	98	1	1	1
102	Alice	95	2	2	1
103	Bob	90	4	3	1
101	Charlie	90	4	3	2
108	Tim	88	5	4	1
104	David	85	6	5	1
170	Greg	67	7	6	1

Observations and SQL Query Syntax:

RANK():

• Chen, with the highest marks of 98, is ranked 1.
• Bob and Charlie, who both scored 90, share the same rank of 4. The next student, Tim, is then ranked 5.

SELECT 
    student_id, 
    name, 
    marks,
    RANK() OVER (ORDER BY marks DESC) AS rank
FROM 
    student_test;

DENSE_RANK():

• Again, Chen is ranked 1.
• Bob and Charlie both have the dense rank of 3. The next student, Tim, gets a dense rank of 4, without any gap.

SELECT 
    student_id, 
    name, 
    marks,
    DENSE_RANK() OVER (ORDER BY marks DESC) AS dense_rank
FROM 
    student_test;

ROW_NUMBER():

• Gives a unique row number to each row. Even though Bob and Charlie have the same marks, they get different row numbers of 1 and 2

SELECT 
    student_id, 
    name, 
    marks,
    ROW_NUMBER() OVER (ORDER BY marks DESC) AS row_number
FROM 
    student_test;

Let me know if you need to help with ranking your data. However, I hope the tutorial gave you everything that you needed to rank your data in SQL.