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

we would type,

Result:

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:

Result:

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:

Result:

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,

Result:

List of SQL Complex Operations