Ranking in SQL
Question
We have the following values
10000
10000
20000
30000
30000
30000
What would be the result of row number, rank, and dense rank ?
solved
3
SQL
55 years
21 Answers
2435 views
Contributor 0
Answers ( 21 )
Sorry it a private answer.
row number
30000 1
30000 2
30000 3
20000 4
10000 5
10000 6
rank
30000 1
30000 1
30000 1
20000 4
10000 5
10000 5
dense rank
30000 1
30000 1
30000 1
20000 2
10000 3
10000 3
row number
30000 1
30000 2
30000 3
20000 4
10000 5
10000 6
rank
30000 1
30000 1
30000 1
20000 4
10000 5
10000 5
dense rank
30000 1
30000 1
30000 1
20000 2
10000 3
10000 3
The only difference between RANK, DENSE_RANK and ROW_NUMBER function is when there are duplicate values in the column being used in ORDER BY Clause .Rank function skips ranks if there is a tie between ranks on the other hand, the DENSE_RANK function does not skip ranks if there is a tie between ranks. Finally, the ROW_NUMBER function has no concern with ranking.
Row number – 1 2 3 4 5
Rank – 1 1 1 4 5 5
Dense Rank – 1 1 1 2 3 3
Ascending = True
Row_number
10000- 1
10000- 2
20000- 3
30000- 4
30000- 5
30000- 6
Rank
10000-1
10000-1
20000-3
30000-4
30000-4
30000-4
Dense_rank
10000-1
10000-1
20000-2
30000-3
30000-3
30000-3
Data-row_number-rank-dense_rank
10000-1-1-1
10000-2-1-1
20000-3-3-2
30000-4-4-3
30000-5-4-3
30000-6-4-3
Row number – 1 2 3 4 5
Rank – 1 1 1 4 5 5
Dense Rank – 1 1 1 2 3 3
Row number – 1 2 3 4 5
Rank – 1 1 1 4 5 5
Dense Rank – 1 1 1 2 3 3
Column_name Row_Num() Rank() Dense_Rank()
30000 1 1 1
30000 2 1 1
30000 3 1 1
20000 4 4 2
10000 5 5 3
10000 6 5 3
Ascending = True
Row_number
1
2
3
4
5
6
Rank
1
1
3
4
4
4
Dense_rank
1
1
2
3
3
3
ROW_NUMBER – ROW_NUMBER function simply returns the row number of the sorted records starting with 1.
30000 1
30000 2
30000 3
20000 4
10000 5
10000 6
RANK – The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause. If there is a tie between N previous records for the value in the ORDER BY column, the RANK functions skips the next N-1 positions before incrementing the counter.
30000 1
30000 1
30000 1
20000 4
10000 5
10000 5
DENSE_RANK – DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records
30000 1
30000 1
30000 1
20000 2
10000 3
10000 3
result of row_number()
30000 1
30000 2
30000 3
20000 4
10000 5
10000 6
result of rank()
30000 1
30000 1
30000 1
20000 4
10000 5
10000 5
result of dense_rank()
30000 1
30000 1
30000 1
20000 2
10000 3
10000 3
Sorry it a private answer.
value row_number rank dense_rank
10000 1 1 1
10000 2 1 1
20000 3 3 2
30000 4 4 3
30000 5 4 3
30000 6 4 3
Ascending =TRUE
Data-row_number-rank-dense_rank
10000-1-1-1
10000-2-1-1
20000-3-3-2
30000-4-4-3
30000-5-4-3
30000-6-4-3
Column_name Row_Num() Rank() Dense_Rank()
30000 1 1 1
30000 2 1 1
30000 3 1 1
20000 4 4 2
10000 5 5 3
10000 6 5 3
Row number : 1 2 3 4 5 6
Rank : 1 1 1 4 5 5
Dense Rank : 1 1 1 2 3 3
10000
10000
20000
30000
30000
30000
NOTE : Ascending Order
1. Result of ROW NUMBER() : {1,2,3,4,5,6}
2. Result of RANK() : {1,1,3,4,4,4}
3. Result of DENSE_RANK() : {1,1,2,3,3,3}
Row_Number() Rank() Dense_Rank()
10000 1 1 1
10000 2 1 1
20000 3 3 2
30000 4 4 3
30000 5 4 3
30000 6 4 3
10000
10000
20000
30000
30000
30000
NOTE : Ascending Order
1. Result of ROW NUMBER() : {1,2,3,4,5,6}
2. Result of RANK() : {1,1,3,4,4,4}
3. Result of DENSE_RANK() : {1,1,2,3,3,3}
row_number-
30000 1
30000 2
30000 3
20000 4
10000 5
10000 6
Rank()
30000 1
30000 1
30000 1
20000 4
10000 5
10000 5
Dense_rank()
30000 1
30000 1
30000 1
20000 2
10000 3
10000 3