Ranking
Learn how to rank rows using SQL.
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:
Press + to interact
SELECT*,ROW_NUMBER() OVER (PARTITION BY city ORDER BY temperature) AS rnFROMtemperaturesORDER BYcity,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 ...