Ranking Functions

Ranking Functions

Ranking with ROW_NUMBER, RANK, and DENSE_RANK

The ranking functions number each partition's rows in the window's ORDER BY and differ on ties. A view (Views) of units sold per product, reused below, has three titles tied at 3:

Three ways to number tied rows, and a rank within each categorySQL

CREATE VIEW units_sold AS SELECT p.id, p.sku, p.category_id, SUM(i.quantity) AS units
FROM products AS p JOIN order_items AS i ON i.product_id = p.id
JOIN orders AS o ON o.id = i.order_id AND o.status <> 'cancelled' GROUP BY p.id;
SELECT category_id AS cat, sku, units, ROW_NUMBER() OVER (ORDER BY units DESC) AS row_num,
       RANK()       OVER (ORDER BY units DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY units DESC) AS dense,
       RANK() OVER (PARTITION BY category_id ORDER BY units DESC) AS in_cat
FROM units_sold ORDER BY units DESC, row_num;

Output

Query OK, 0 rows affected (0.013 sec)
+-----+-----------+-------+---------+-----+-------+--------+
| cat | sku       | units | row_num | rnk | dense | in_cat |
+-----+-----------+-------+---------+-----+-------+--------+
|   5 | AC-MUG-01 |     6 |       1 |   1 |     1 |      1 |
|   5 | AC-STK-01 |     4 |       2 |   2 |     2 |      2 |
|   2 | BK-PHP-01 |     3 |       3 |   3 |     3 |      1 |
|   3 | BK-SQL-01 |     3 |       4 |   3 |     3 |      1 |
|   2 | BK-LAR-01 |     3 |       5 |   3 |     3 |      1 |
|   2 | BK-LNX-01 |     2 |       6 |   6 |     4 |      3 |
|   3 | BK-SQL-02 |     1 |       7 |   7 |     5 |      2 |
+-----+-----------+-------+---------+-----+-------+--------+
7 rows in set (0.001 sec)

ROW_NUMBER never repeats, so its order among ties is arbitrary unless you add a tiebreaker such as id. RANK shares a number and skips; DENSE_RANK shares without skipping. Wrap the query in a CTE and keep in_cat = 1, and you have the top product per category COUNT, SUM, AVG, MIN, and MAX could not write, both tied Programming titles included.

MENU