SQL rank functions are used to determine the position of a value in a list/table
What Does a Rank Function Do?
Sql rank functions help us to assign a rank or a number to each row in a result set based on a specified order, which will be helpful to organize and analyze data.
- Highest number gets rank 1 (in descending order).
- Lowest number gets rank 1 (in ascending order), depending on how you want to sort it
They help us to figure out:
- Who came first, second, third, etc.
- Which items are tied or repeated?
- How each row compares to the other
Why Do We Need Rank Functions?
Ranking helps in situations like:
- Finding top performers (e.g., top students, top salespeople).
- Identifying trends (e.g., products by popularity).
- Analyzing competition (e.g., leaderboard positions).
- Prioritizing tasks/data based on scores or metrics
Example :
Let us assume we have a student table with columns student name, department and marks
| Student name | Department | Marks |
| Ram | Cse | 89 |
| Tom | Ece | 90 |
| John | Ece | 34 |
| Ravi | Mech | 65 |
| Kiran | Cse | 45 |
| Raji | Mech | 89 |
| Mona | Civil | 100 |
| Sita | cse | 65 |
| Roja | Mech | 45 |
In this table
We can identify
Which student has got the highest mark and lowest mark totally and also, we can get highest and lowest mark in each department by using this rank functions…?
The most commonly used ranking functions in Sql are:
1. Rank():
It assigns a rank to each row within a result set based on a specified ordering (ascending or descending).
If there are ties (i.e., duplicate values in the ordering column), they receive the same rank, and the next rank is skipped accordingly.
| Sales | Rank() |
| 89 | 2 |
| 90 | 1 |
| 65 | 4 |
| 45 | 5 |
| 89 | 2 |
In the above table, for sale value 90 it is ranked 1,
next highest sale value 89 but it is repeated twice so rank 2 will be twice hence 3 will be skipped and second rank has come 2 times
next highest sale value is 65, it will be ranked as 4
next highest sale value is 45, it will be ranked as 5
SYNTAX:
RANK() OVER (
[PARTITION BY partition_expression]
ORDER BY sort_expression [ASC | DESC]
)
Components
PARTITION BY: (optional) divides the result set into partitions (like groups). The rank is reset for each partition.
ORDER BY: defines the order of rows within each partition.
NOTE:
In order by asc is optional even if you do not mention asc | desc by default it will be asc
SAMPLE DATA:
select * from students;
| StudentName | Department | Marks |
| Ram | Cse | 89 |
| Tom | Ece | 90 |
| John | Ece | 34 |
| Ravi | Mech | 65 |
| Kiran | Cse | 45 |
| Raji | Mech | 89 |
| Mona | Civil | 100 |
| Sita | Cse | 65 |
| Roja | Mech | 45 |
EXAMPLE WITHOUT USING PARTITION:
a) In Ascending order
select *, rank() over(order by marks) from students;
| StudentName | Department | Marks | rank() over(order by marks) |
| John | Ece | 34 | 1 |
| Kiran | Cse | 45 | 2 |
| Roja | Mech | 45 | 2 |
| Ravi | Mech | 65 | 4 |
| Sita | Cse | 65 | 4 |
| Ram | Cse | 89 | 6 |
| Raji | Mech | 89 | 6 |
| Tom | Ece | 90 | 8 |
| Mona | Civil | 100 | 9 |
b) In Descending order
select *, rank() over(order by marks desc) from students;
| StudentName | Department | Marks | rank() over(order by marks desc) |
| Mona | Civil | 100 | 1 |
| Tom | Ece | 90 | 2 |
| Ram | Cse | 89 | 3 |
| Raji | Mech | 89 | 3 |
| Ravi | Mech | 65 | 5 |
| Sita | Cse | 65 | 5 |
| Kiran | Cse | 45 | 7 |
| Roja | Mech | 45 | 7 |
| John | Ece | 34 | 9 |
EXAMPLE WITH PARTITION:
a) In Ascending order
select *, rank() over(partition by department order by marks) as partition_rank from students;
| StudentName | Department | Marks | partition_rank |
| Mona | Civil | 100 | 1 |
| Kiran | Cse | 45 | 1 |
| Sita | Cse | 65 | 2 |
| Ram | Cse | 89 | 3 |
| John | Ece | 34 | 1 |
| Tom | Ece | 90 | 2 |
| Roja | Mech | 45 | 1 |
| Ravi | Mech | 65 | 2 |
| Raji | Mech | 89 | 3 |
b) In Descending order
select *, rank() over(partition by department order by marks desc) as partition_rank from students;
| StudentName | Department | Marks | partition_rank |
| Mona | Civil | 100 | 1 |
| Ram | Cse | 89 | 1 |
| Sita | Cse | 65 | 2 |
| Kiran | Cse | 45 | 3 |
| Tom | Ece | 90 | 1 |
| John | Ece | 34 | 2 |
| Raji | Mech | 89 | 1 |
| Ravi | Mech | 65 | 2 |
| Roja | Mech | 45 | 3 |
2. Dense_Rank():
It assigns a rank to each row within a result set based on a specified ordering (ascending or descending).
If there are ties (i.e., duplicate values in the ordering column), they receive the same rank, and the next rank is continued will not be skipped.
| Sales | Dense_Rank() |
| 89 | 2 |
| 90 | 1 |
| 65 | 3 |
| 45 | 4 |
| 89 | 2 |
In the above table, for sale value 90 it is ranked 1,
next highest sale value 89 but it is repeated twice so rank 2 will be twice
next highest sale value is 65, it will be ranked as 3
next highest sale value is 45, it will be ranked as 4
SYNTAX:
Dense_RANK() OVER (
[PARTITION BY partition_expression]
ORDER BY sort_expression [ASC | DESC]
)
Components:
PARTITION BY: (optional) divides the result set into partitions (like groups). The rank is reset for each partition.
ORDER BY: defines the order of rows within each partition.
NOTE:
In order by asc is optional even if you do not mention asc | desc by default it will be asc
EXAMPLE WITHOUT USING PARTITION:
a) In Ascending order
select *, dense_rank() over(order by marks) from students;
| StudentName | Department | Marks | dense_rank() over(order by marks) |
| John | Ece | 34 | 1 |
| Kiran | Cse | 45 | 2 |
| Roja | Mech | 45 | 2 |
| Ravi | Mech | 65 | 3 |
| Sita | Cse | 65 | 3 |
| Ram | Cse | 89 | 4 |
| Raji | Mech | 89 | 4 |
| Tom | Ece | 90 | 5 |
| Mona | Civil | 100 | 6 |
b) In Descending order
select *, dense_rank() over(order by marks desc) from students;
| StudentName | Department | Marks | dense_rank() over(order by marks desc) |
| Mona | Civil | 100 | 1 |
| Tom | Ece | 90 | 2 |
| Ram | Cse | 89 | 3 |
| Raji | Mech | 89 | 3 |
| Ravi | Mech | 65 | 4 |
| Sita | Cse | 65 | 4 |
| Kiran | Cse | 45 | 5 |
| Roja | Mech | 45 | 5 |
| John | Ece | 34 | 6 |
EXAMPLE WITH PARTITION:
a) In Ascending order
select *, dense_rank() over(partition by department order by marks) as denserank from students;
| StudentName | Department | Marks | denserank |
| Mona | Civil | 100 | 1 |
| Kiran | Cse | 45 | 1 |
| Sita | Cse | 65 | 2 |
| Ram | Cse | 89 | 3 |
| John | Ece | 34 | 1 |
| Tom | Ece | 90 | 2 |
| Roja | Mech | 45 | 1 |
| Ravi | Mech | 65 | 2 |
| Raji | Mech | 89 | 3 |
b) In Descending order
select *, dense_rank() over(partition by department order by marks desc ) as denserank from students;
| StudentName | Department | Marks | denserank |
| Mona | Civil | 100 | 1 |
| Ram | Cse | 89 | 1 |
| Sita | Cse | 65 | 2 |
| Kiran | Cse | 45 | 3 |
| Tom | Ece | 90 | 1 |
| John | Ece | 34 | 2 |
| Raji | Mech | 89 | 1 |
| Ravi | Mech | 65 | 2 |
| Roja | Mech | 45 | 3 |
3. Row_number():
It assigns a rank to each row within a result set based on a specified ordering (ascending or descending) as well as based on insertion order of row.
If there are ties (i.e., duplicate values in the ordering column), they receive the different rank, the value in ties which are at top rows will be receiving the highest rank and the rows which are at next rows will be receiving next rank
| Sales | Row_number() |
| 89 | 2 |
| 90 | 1 |
| 65 | 4 |
| 45 | 5 |
| 89 | 3 |
In the above table, for sale value 90 it is ranked 1,
next highest sale value 89 but it is repeated twice first sales is in first row and other is in last row hence the sales which is in first row will be getting rank as 2 and sales at last row will be getting rank as 3
next highest sale value is 65, it will be ranked as 4
next highest sale value is 45, it will be ranked as 5
SYNTAX:
row_number() OVER (
[PARTITION BY partition_expression]
ORDER BY sort_expression [ASC | DESC]
)
Components:
PARTITION BY: (optional) divides the result set into partitions (like groups). The rank is reset for each partition.
ORDER BY: defines the order of rows within each partition.
NOTE:
In order by asc is optional even if you do not mention asc | desc by default it will be asc
EXAMPLE WITHOUT USING PARTITION:
a) In Ascending order
select *, row_number() over(order by marks) as rowno from students;
| StudentName | Department | Marks | rowno |
| John | Ece | 34 | 1 |
| Kiran | Cse | 45 | 2 |
| Roja | Mech | 45 | 3 |
| Ravi | Mech | 65 | 4 |
| Sita | Cse | 65 | 5 |
| Ram | Cse | 89 | 6 |
| Raji | Mech | 89 | 7 |
| Tom | Ece | 90 | 8 |
| Mona | Civil | 100 | 9 |
b) In Descending order
select *, row_number() over(order by marks desc) as rowno from students;
| StudentName | Department | Marks | rowno |
| Mona | Civil | 100 | 1 |
| Tom | Ece | 90 | 2 |
| Ram | Cse | 89 | 3 |
| Raji | Mech | 89 | 4 |
| Ravi | Mech | 65 | 5 |
| Sita | Cse | 65 | 6 |
| Kiran | Cse | 45 | 7 |
| Roja | Mech | 45 | 8 |
| John | Ece | 34 | 9 |
EXAMPLE WITH PARTITION:
a) In Ascending order
select *, row_number() over(partition by department order by marks) as rowno from students;
| StudentName | Department | Marks | rowno |
| Mona | Civil | 100 | 1 |
| Kiran | Cse | 45 | 1 |
| Sita | Cse | 65 | 2 |
| Ram | Cse | 89 | 3 |
| John | Ece | 34 | 1 |
| Tom | Ece | 90 | 2 |
| Roja | Mech | 45 | 1 |
| Ravi | Mech | 65 | 2 |
| Raji | Mech | 89 | 3 |
b) In Descending order
select *, row_number() over(partition by department order by marks desc) as rowno from students;
| StudentName | Department | Marks | rowno |
| Mona | Civil | 100 | 1 |
| Ram | Cse | 89 | 1 |
| Sita | Cse | 65 | 2 |
| Kiran | Cse | 45 | 3 |
| Tom | Ece | 90 | 1 |
| John | Ece | 34 | 2 |
| Raji | Mech | 89 | 1 |
| Ravi | Mech | 65 | 2 |
| Roja | Mech | 45 | 3 |
4. NTILE(n):
It divides the result set into n equal or non-equal parts (n value is given by user), each part will be getting one value
When all the rows in result set are divided into equal parts if that number is not an integer number, then it will be rounding up to nearest even number.
Depending on that integer number the no of rows in one part will be decided
| Sales | Ntile(n) |
| 89 | 1 |
| 90 | 1 |
| 65 | 2 |
| 45 | 2 |
| 89 | 3 |
In the above table, there are 5 rows
I would like to divide it into 3 parts hence n=3
5/3=1.6
1.6 will be rounding to 2
Therefore, the result set is divided into 3 parts with 2 rows in each part, but the last part will be having one row as the no of rows are odd
SYNTAX:
ntile(n) OVER (
[PARTITION BY partition_expression]
ORDER BY sort_expression [ASC | DESC]
)
Components:
PARTITION BY: (optional) divides the result set into partitions (like groups). The rank is reset for each partition.
ORDER BY: defines the order of rows within each partition.
NOTE:
In order by asc is optional even if you do not mention asc | desc by default it will be asc
EXAMPLE WITHOUT USING PARTITION:
a) In Ascending order
select *, ntile(3) over(order by marks) from students;
| StudentName | Department | Marks | ntile(3) over(order by marks) |
| John | Ece | 34 | 1 |
| Kiran | Cse | 45 | 1 |
| Roja | Mech | 45 | 1 |
| Ravi | Mech | 65 | 2 |
| Sita | Cse | 65 | 2 |
| Ram | Cse | 89 | 2 |
| Raji | Mech | 89 | 3 |
| Tom | Ece | 90 | 3 |
| Mona | Civil | 100 | 3 |
b) In Descending order
select *,ntile(3) over(order by marks desc) from students;
| StudentName | Department | Marks | ntile(3) over(order by marks desc) |
| Mona | Civil | 100 | 1 |
| Tom | Ece | 90 | 1 |
| Ram | Cse | 89 | 1 |
| Raji | Mech | 89 | 2 |
| Ravi | Mech | 65 | 2 |
| Sita | Cse | 65 | 2 |
| Kiran | Cse | 45 | 3 |
| Roja | Mech | 45 | 3 |
| John | Ece | 34 | 3 |
EXAMPLE WITH PARTITION:
a) In Ascending order
select *,ntile(3) over(partition by department order by marks) as ranking from students;
| StudentName | Department | Marks | ranking |
| Mona | Civil | 100 | 1 |
| Kiran | Cse | 45 | 1 |
| Sita | Cse | 65 | 2 |
| Ram | Cse | 89 | 3 |
| John | Ece | 34 | 1 |
| Tom | Ece | 90 | 2 |
| Roja | Mech | 45 | 1 |
| Ravi | Mech | 65 | 2 |
| Raji | Mech | 89 | 3 |
b) In Descending order
select *, ntile(3) over(partition by department order by marks desc) as ranking from students;
| StudentName | Department | Marks | ranking |
| Mona | Civil | 100 | 1 |
| Ram | Cse | 89 | 1 |
| Sita | Cse | 65 | 2 |
| Kiran | Cse | 45 | 3 |
| Tom | Ece | 90 | 1 |
| John | Ece | 34 | 2 |
| Raji | Mech | 89 | 1 |
| Ravi | Mech | 65 | 2 |
| Roja | Mech | 45 | 3 |
