SQL > Advanced SQL > Rank

There are several use cases in SQL where there are multiple ways to solve the problem. Showing the rank of values is one such case. Here we will show two ways of calculating the rank. The first without using the window function, the second using the window function.

Not using window function

The general idea to display rank in SQL is to do a self-join, then list out the results in order, and finally do a count on the number of records that's listed ahead of (and including) the record of interest. Let's use an example to illustrate. Say we have the following table,

Table Total_Sales

 Name  Sales 
 John  10 
 Jennifer  15 
 Stella  20 
 Sophia  40 
 Greg  50 
 Jeff  20 

we would type,

SELECT a1.Name, a1.Sales, COUNT (a2.Sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name Sales Sales_Rank
Greg 50 1
Sophia 40 2
Stella 20 3
Jeff 20 3
Jennifer 15 5
John 10 6

Please note that in this case, there WHERE clause accounts for the possibility that multiple rows of data have the same value of in the Sales column. If we know for sure that there are no duplicate values in the Sales column, we can change the WHERE clause to:

WHERE a1.Sales <= a2.Sales

Using window function

A second way to calculate rank is via a window function. In this case, we will use the following:

SELECT a1.Name, Sales, RANK() over (ORDER BY Sales DESC) RK
FROM Total_Sales
ORDER BY a1.Sales DESC;

Result:

Name Sales RK
Greg  50 1
Sophia  40 2
Stella  20 3
Jeff  20 3
Jennifer  15 5
John  10 6

When you use the RANK() function, two rows that are tied will share the same rank. This function will consider the number of rows that are tied and assign the rank to the subsequent rows. In this example, two rows are tied for 3rd, so the next row, Jennifer, gets a rank of 5.

You can also use ROW_NUMBER() function in the window function. In this case, the SQL will be as follows:

SELECT a1.Name, Sales, ROW_NUMBER() over (ORDER BY Sales DESC) RK
FROM Total_Sales
ORDER BY a1.Sales DESC;

Result:

Name Sales RK
Greg 501
Sophia 402
Stella 203
Jeff 204
Jennifer 155
John 106

Note that in this example, it is equally likely that Stella or Jeff will show up first and get rank 3, because the only ORDER BY criteria is Sales, which produces a tie between those two rows. If you have a specific criteria to break the tie, make sure to include that in the ORDER BY. For example, if you want the results to be displayed alphabetically by Name in case of a tie, you will type in,

SELECT a1.Name, Sales, ROW_NUMBER() over (ORDER BY Sales DESC, Name) RK
FROM Total_Sales
ORDER BY a1.Sales DESC;

Result:

Name Sales Sales_Rank
Greg 501
Sophia 402
Jeff 203
Stella 204
Jennifer 155
John< /td>106

List of SQL Complex Operations

 Operation Description
 Rank  Calculates the ranking of a series of numbers. 
 Median  Calculates the median of a series of numbers. 
 Running Totals  Calculates the running total for a series of numbers. 
 Percent To Total  Calculates the percent to total for each number in a series. 
 Cumulative Percent To Total  Calculates the cumulative percent to total for each number in a series. 


Next: SQL Median

This page was last updated on June 23, 2022.




Copyright © 2022   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact