RANK vs DENSE_RANK vs ROW_NUMBER in SQL Server

ROW_NUMBER is for “one row per group.” RANK is for a league table that skips after a tie (1, 1, 3). DENSE_RANK is 1, 1, 2.

Products in a category

SELECT category, product_name, units_sold,
       RANK() OVER (PARTITION BY category ORDER BY units_sold DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY units_sold DESC) AS dense,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY units_sold DESC, product_id) AS rn
FROM ProductSales;

For “latest order per customer” use ROW_NUMBER, not RANK. Two orders on the same day would both be rank 1.