Ranks and Ties
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 12 of 25.
RANK gives peers the same rank and leaves gaps after ties. DENSE_RANK gives peers the same rank without gaps. Peers are rows equal on every expression in the window ORDER BY.
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT id, amount, RANK() OVER (ORDER BY amount DESC) AS rank_value, DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_value FROM orders ORDER BY amount DESC, id;Amounts 50, 30, 30, 20, 10 get ranks 1, 2, 2, 4, 5 and dense ranks 1, 2, 2, 3, 4. Adding id inside the window ORDER BY would break the amount ties; put it only in the outer sort.
Choose RANK for gaps after ties and DENSE_RANK for consecutive distinct-value ranks.
Challenge
MediumReturn id, amount and amount_rank using dense ranking from highest amount to lowest. Equal amounts must share a rank. Display by amount descending, then id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
3Window Calculations
Number Rows in GroupsRanks and TiesRunning TotalsCompare with LAGLatest Order per CustomerPractice on your own: PostgreSQL playground