Master MySQL Ranking: row_number, rank, and dense_rank Explained

Understanding Window Functions

Window functions (OLAP functions) allow complex analysis over a set of rows defined by a "window". In MySQL they are used together with the OVER clause.

Ranking Functions – row_number

row_number()

assigns a unique sequential number to each row within the window.

ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)

Example: rank users by total purchase amount.

SELECT *, ROW_NUMBER() OVER (ORDER BY sale_money DESC) AS `rank`
FROM (
    SELECT user_id,
           SUM(pay_money - IFNULL(refund_money,0)) AS sale_money,
           SUM(commission) AS commission,
           SUM(goods_num) AS sale_num
    FROM `order`
    WHERE is_deleted = 0
      AND order_time >= '2025-06-17 00:00:00'
      AND order_time <= '2025-07-18 00:00:00'
    GROUP BY order_id
) t
GROUP BY user_id
ORDER BY sale_money DESC;

The result shows each row gets a distinct rank even when sale_money values are identical.

Ranking Functions – rank

rank()

gives the same rank to rows with equal values, and skips subsequent ranks (creates gaps). RANK() OVER (ORDER BY sale_money DESC) Using rank() on the same data groups equal sale_money values together, but the next rank jumps.

Ranking Functions – dense_rank

dense_rank()

also gives equal rows the same rank, but does not create gaps; the next rank increments by one. DENSE_RANK() OVER (ORDER BY sale_money DESC) Applying dense_rank() yields consecutive ranks for identical sale_money values.

Summary

In data analysis, row_number(), rank(), and dense_rank() are useful tools for ranking and classification. row_number() provides a unique sequential number, rank() handles ties with gaps, and dense_rank() handles ties without gaps, allowing you to choose the appropriate function for your ranking requirements.