Search⌘ K
AI Features
Home
Courses
Practical Data Analysis with SQL

Ranking

Explore SQL ranking functions like ROW_NUMBER, RANK, and DENSE_RANK to rank data sets with and without gaps. Understand how to apply deterministic sorting for consistent, meaningful rankings in data analysis using PostgreSQL.

We'll cover the following...

Overview

Ranking is an extremely common task. Ranking employees by performance, products by popularity, or students by score, are all different ways to use ranking.

Row numbers

The most basic form of ranking is adding a row number to each row. In PostgreSQL, we can add row numbers in a specific sort order using the ROW_NUMBER window function:

PostgreSQL
SELECT
  *,
  ROW_NUMBER() OVER (PARTITION BY city ORDER BY temperature) AS rn
FROM
  temperatures
ORDER BY
  city,
  rn;

The window function, ROW_NUMBER, generates row numbers for each group of rows with the same city. To rank rows according to the temperature, we use ORDER BY temperature in ascending order. As a result, the coldest day in ...