Understanding the rank() and dense_rank() Functions in PostgreSQL

In PostgreSQL, rank() and dense_rank() are general-purpose window functions that are used to order values and assign them rank in the form of numbers on where they fall in relation to each other.

Both of these functions are used with an OVER clause along with either PARTITION BY, ORDER BY, or ROWS BETWEEN. When used with rank() and dense_rank(), they affect the windows in these ways:

• ORDER BY: specifies the column whose values you wish to rank

• PARTITION BY: groups the rankings

The difference between rank() and dense_rank() is in how they handle identical values. Let’s say that you are assigning rank based on a grade value, and you end up with two results that are both equal to 85. If you use rank(), both of these values will be given the same rank, and the next rank will be skipped. So if they both tied for 3, then they will both be given a 3, 4 will be skipped, and the next highest rank will be 5. If you use dense_rank() on the same example, 4 won’t be skipped. It will be the next rank value.

rank() syntax:

rank () → bigint

dense_rank() syntax:

dense_rank () → bigint

rank() syntax with window functions:

rank() OVER ([PARTITION BY <columns>] [ORDER BY <columns>])

dense_rank() syntax with window functions:

dense_rank() OVER ([PARTITION BY <columns>] [ORDER BY <columns>])

rank() and dense_rank() in PostgreSQL: Examples

The data set that we will be using for examples contains the grades of three students in four subjects.

Find the rank of every row

If we wanted to know which students needed the most help in specific subjects, we could run a query like this:

SELECT student, subject, grade, rank() OVER(ORDER BY grade DESC) FROM grades; Copy

We want the rank of 1 to go to the highest grade. In order to do that, we order the results in the window frame in descending order so the highest grades are at the top.

Here are the results:

From this, we can tell that Jim needs help in both History and English even though he is at the top of the class in Math. Sam also needs help with History. Now, you will notice that both 7 and 10 are repeated, and there is no 8 or 11. This is because we are using rank(). When we use dense_rank() instead, there will be no gaps.

Here is that result:

Using partitions with rank()

If we want to see how each student ranks per subject, we can add a PARTITION BY clause, which can group the records in the window. Here is the query:

SELECT student, subject, RANK() OVER(PARTITION BY subject ORDER BY grade DESC) FROM grades ORDER BY student, rank; Copy

And here are the results:

Finding the rank of an average

To see how each student’s average grade across all the subjects rank, we can use a subquery to do that. Here is that query:

SELECT student, average, RANK() OVER(ORDER BY average DESC) FROM ( SELECT student, avg(grade) average FROM grades GROUP BY student) AS subquery; Copy

And here are the results:

Finding the rank of a value in grouped rows

For this example, let’s use another data set. This one contains the temperature and precipitation data for a couple of cities.

Let’s say we have a temperature, and we want to see where it ranks in relation to the temperatures in our table. For example, we want to see how 68 degrees ranks in both Miami and Atlanta for this range of dates.

Here is the query:

SELECT city, rank(68) WITHIN GROUP ( ORDER BY temperature DESC) FROM city_data GROUP BY city; Copy

We grouped the results by the city so we get the rank of 68 degrees in each. Then we use the WITHIN GROUP clause, which specifies how to sort the rows that are grouped by the aggregate function. Here, we order the rows by the descending temperature because we want the highest temperature to get a rank of 1.

Here are the results:

This tells us that 68 degrees would be the second-highest temperature for Atlanta and the fourth-highest temperature in Miami for the date range in the table.

Next Steps

To learn more about rank() and dense_rank() and how to use them in PostgreSQL, check out PostgreSQL’s documentation on window functions. For more examples on how to use them in your own TimescaleDB SQL queries, see these Timescale documentation sections:

• Perform advanced analytic queries

• Introduction to IoT: New York City Taxicabs