{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"ranking-functions","title":"ROW_NUMBER, RANK and DENSE_RANK: three answers to \"what position\"","url":"https://addaly.com/learn/data-and-sql/ranking-functions","position":35,"of":66,"minutes":8,"answer":"row_number breaks every tie arbitrarily unless you give it a tiebreaker, rank shares positions and skips, dense_rank shares and does not skip.","keyIdea":"row_number breaks every tie arbitrarily unless you give it a tiebreaker, rank shares positions and skips, dense_rank shares and does not skip, and the row count of a filtered ranking tells you which one you actually needed."},"body":{"format":"markdown","text":"## Four students, two of them tied\n\nScores: Asha 90, Bilal 85, Chen 85, Dev 80. Ask for their positions and there are three defensible answers, and SQL gives you all three as separate functions.\n\n```sql\nSELECT name, score,\n       row_number() OVER (ORDER BY score DESC) AS rn,\n       rank()       OVER (ORDER BY score DESC) AS rnk,\n       dense_rank() OVER (ORDER BY score DESC) AS drnk\nFROM results;\n```\n\n- `row_number()` gives 1, 2, 3, 4. Every row gets a distinct number. Bilal and Chen are tied, and one of them gets 2 and the other 3 by an order the query did not specify.\n- `rank()` gives 1, 2, 2, 4. Ties share a position, and the next position skips ahead by the number of tied rows, because two people came second and so nobody came third.\n- `dense_rank()` gives 1, 2, 2, 3. Ties share a position and nothing is skipped.\n\nNone of these is more correct. A sports league uses `rank()`: two teams level on points are joint second, and the next is fourth. A \"top three products\" list that should show every product that made the top three uses `dense_rank()`. A pagination key or a deduplication step uses `row_number()`, because it needs every row to have a different number.\n\n## row_number needs a complete order\n\nBecause `row_number()` must break every tie, it will break them arbitrarily if you let it. Two runs of the same query can give Bilal 2 then 3. In a report that is a cosmetic wobble; in a query that keeps the row where `rn = 1` and deletes the rest, it decides which row survives. Always add a tiebreaker that cannot tie, usually the primary key:\n\n```sql\nrow_number() OVER (PARTITION BY customer_id ORDER BY placed_at DESC, order_id DESC)\n```\n\nNow \"the customer's latest order\" is the same row every time, and when two orders share a timestamp the higher id wins, which is a rule you can explain.\n\n## Where NULLs rank\n\nSorting descending puts NULLs first in Postgres, because NULL is treated as larger than every value, and a student with no score would be ranked top of the class. Say what you mean: `ORDER BY score DESC NULLS LAST`. MySQL and SQLite treat NULL as smallest, so the same query ranks the missing score last, which is a difference that shows up only when the data has gaps.\n\n> Figure: Five products, two ties, three different \"top three\". Bars (rows returned by a filter at three): row_number() <= 3 3; rank() <= 3 3; dense_rank() <= 3 5. Sales are 900, 700, 700, 500 and 500. row_number numbers them 1 to 5 and breaks the ties on nothing in particular. rank gives 1, 2, 2, 4, 4. dense_rank gives 1, 2, 2, 3, 3, so a filter at three lets both 500s through. Count the rows a ranking picked; that count tells you which function you actually needed.\n\n## Cutting into equal groups\n\n`ntile(4) OVER (ORDER BY spend DESC)` assigns each customer to a quartile: the top quarter of spenders get 1, the next quarter 2, and so on. When the rows do not divide evenly the earlier groups get the extra: ten rows into four tiles gives groups of 3, 3, 2, 2. It is the tool for \"split customers into deciles by revenue\" and it is also how a lot of segmentation gets done badly, because `ntile` produces groups of equal *count*, not equal *value*. The top decile by count may hold 60% of revenue. If you want groups of equal revenue, you need a running total and a cut at each tenth of the sum, which the running-totals lesson makes straightforward.\n\n## Position as a fraction\n\n`percent_rank()` returns 0 for the first row and 1 for the last, with each row placed by how many rows fall before it. `cume_dist()` returns the share of rows with a value at or below this one. A student at `cume_dist` 0.9 scored as well as or better than 90% of the class. These are how \"you are in the top 5%\" is computed, and they inherit the tie behaviour of `rank()`, so tied students get the same fraction.\n\n## Rank within a group\n\nAdd a partition and the ranking restarts in each group:\n\n```sql\nrank() OVER (PARTITION BY class_id ORDER BY score DESC)\n```\n\nNow every class has its own first place. This is the pattern under every \"best in category\" question, and the next lesson turns it into a filter.\n\n## The check that catches the wrong function\n\nIf your ranking is used to pick rows, count the rows it picked. `rank() <= 3` can return five rows when there are ties; `row_number() <= 3` returns exactly three and may have excluded a tied row on the basis of nothing. Neither is wrong. One of them is what was asked for, and the count is how you find out which you built."},"check":{"question":"Five products have monthly sales of 500, 400, 400, 300 and 300. A report filters on `rank() OVER (ORDER BY sales DESC) <= 3` to show the top three. How many rows does it return?","options":[{"text":"Exactly three, because rank stops at position 3 and the tied 300s are ranked 4 and 5.","correct":false,"tempting":"The two 300s share rank 4 after the tied 400s took ranks 2 and 2, so neither is in the top three; this gets the count right by accident of the wrong mechanism and is still wrong about the 400s."},{"text":"Five, because rank never skips positions, so the 300s are ranked 3 and the filter includes everything.","correct":false,"tempting":"Describes dense_rank; rank skips position 3 entirely because two products occupy position 2."},{"text":"Two, because rank assigns tied rows the same position and then discards all but one of them.","correct":false,"tempting":"Rank never discards rows; every tied row keeps its shared position and is returned."},{"text":"Three: the 500 at rank 1 and both 400s at rank 2, with the 300s at rank 4 and excluded.","correct":true}]},"prev":{"slug":"what-a-window-is","title":"A window: GROUP BY that keeps the rows","url":"https://addaly.com/learn/data-and-sql/what-a-window-is"},"next":{"slug":"top-n-per-group","title":"Top N per group in two lines","url":"https://addaly.com/learn/data-and-sql/top-n-per-group"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'ROW_NUMBER, RANK and DENSE_RANK: three answers to \"what position\"', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/ranking-functions (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"ROW_NUMBER, RANK and DENSE_RANK: three answers to “what position”","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"row_number breaks every tie arbitrarily unless you give it a tiebreaker, rank shares positions and skips, dense_rank shares and does not skip, and the row count of a filtered ranking tells you which one you actually needed.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"bars","unit":"rows returned by a filter at three","title":"Five products, two ties, three different “top three”","series":[{"label":"row_number() <= 3","value":3},{"label":"rank() <= 3","value":3},{"label":"dense_rank() <= 3","value":5,"highlight":true}],"caption":"Sales are 900, 700, 700, 500 and 500. row_number numbers them 1 to 5 and breaks the ties on nothing in particular. rank gives 1, 2, 2, 4, 4. dense_rank gives 1, 2, 2, 3, 3, so a filter at three lets both 500s through. Count the rows a ranking picked; that count tells you which function you actually needed."},"alt":"Figure: Five products, two ties, three different “top three”. Bars (rows returned by a filter at three): row_number() <= 3 3; rank() <= 3 3; dense_rank() <= 3 5. Sales are 900, 700, 700, 500 and 500. row_number numbers them 1 to 5 and breaks the ties on nothing in particular. rank gives 1, 2, 2, 4, 4. dense_rank gives 1, 2, 2, 3, 3, so a filter at three lets both 500s through. Count the rows a ranking picked; that count tells you which function you actually needed."}},"blocks":[{"t":"h2","id":"four-students-two-of-them-tied","runs":[{"text":"Four students, two of them tied"}]},{"t":"p","runs":[{"text":"Scores: Asha 90, Bilal 85, Chen 85, Dev 80. Ask for their positions and there are three defensible answers, and SQL gives you all three as separate functions."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT name, score,\n       row_number() OVER (ORDER BY score DESC) AS rn,\n       rank()       OVER (ORDER BY score DESC) AS rnk,\n       dense_rank() OVER (ORDER BY score DESC) AS drnk\nFROM results;"},{"t":"ul","items":[[{"text":"row_number()","code":true},{"text":" gives 1, 2, 3, 4. Every row gets a distinct number. Bilal and Chen are tied, and one of them gets 2 and the other 3 by an order the query did not specify."}],[{"text":"rank()","code":true},{"text":" gives 1, 2, 2, 4. Ties share a position, and the next position skips ahead by the number of tied rows, because two people came second and so nobody came third."}],[{"text":"dense_rank()","code":true},{"text":" gives 1, 2, 2, 3. Ties share a position and nothing is skipped."}]]},{"t":"p","runs":[{"text":"None of these is more correct. A sports league uses "},{"text":"rank()","code":true},{"text":": two teams level on points are joint second, and the next is fourth. A “top three products” list that should show every product that made the top three uses "},{"text":"dense_rank()","code":true},{"text":". A pagination key or a deduplication step uses "},{"text":"row_number()","code":true},{"text":", because it needs every row to have a different number."}]},{"t":"h2","id":"rownumber-needs-a-complete-order","runs":[{"text":"row_number needs a complete order"}]},{"t":"p","runs":[{"text":"Because "},{"text":"row_number()","code":true},{"text":" must break every tie, it will break them arbitrarily if you let it. Two runs of the same query can give Bilal 2 then 3. In a report that is a cosmetic wobble; in a query that keeps the row where "},{"text":"rn = 1","code":true},{"text":" and deletes the rest, it decides which row survives. Always add a tiebreaker that cannot tie, usually the primary key:"}]},{"t":"code","lang":"sql","label":"SQL","text":"row_number() OVER (PARTITION BY customer_id ORDER BY placed_at DESC, order_id DESC)"},{"t":"p","runs":[{"text":"Now “the customer’s latest order” is the same row every time, and when two orders share a timestamp the higher id wins, which is a rule you can explain."}]},{"t":"h2","id":"where-nulls-rank","runs":[{"text":"Where NULLs rank"}]},{"t":"p","runs":[{"text":"Sorting descending puts NULLs first in Postgres, because NULL is treated as larger than every value, and a student with no score would be ranked top of the class. Say what you mean: "},{"text":"ORDER BY score DESC NULLS LAST","code":true},{"text":". MySQL and SQLite treat NULL as smallest, so the same query ranks the missing score last, which is a difference that shows up only when the data has gaps."}]},{"t":"h2","id":"cutting-into-equal-groups","runs":[{"text":"Cutting into equal groups"}]},{"t":"p","runs":[{"text":"ntile(4) OVER (ORDER BY spend DESC)","code":true},{"text":" assigns each customer to a quartile: the top quarter of spenders get 1, the next quarter 2, and so on. When the rows do not divide evenly the earlier groups get the extra: ten rows into four tiles gives groups of 3, 3, 2, 2. It is the tool for “split customers into deciles by revenue” and it is also how a lot of segmentation gets done badly, because "},{"text":"ntile","code":true},{"text":" produces groups of equal "},{"text":"count","i":true},{"text":", not equal "},{"text":"value","i":true},{"text":". The top decile by count may hold 60% of revenue. If you want groups of equal revenue, you need a running total and a cut at each tenth of the sum, which the running-totals lesson makes straightforward."}]},{"t":"h2","id":"position-as-a-fraction","runs":[{"text":"Position as a fraction"}]},{"t":"p","runs":[{"text":"percent_rank()","code":true},{"text":" returns 0 for the first row and 1 for the last, with each row placed by how many rows fall before it. "},{"text":"cume_dist()","code":true},{"text":" returns the share of rows with a value at or below this one. A student at "},{"text":"cume_dist","code":true},{"text":" 0.9 scored as well as or better than 90% of the class. These are how “you are in the top 5%” is computed, and they inherit the tie behaviour of "},{"text":"rank()","code":true},{"text":", so tied students get the same fraction."}]},{"t":"h2","id":"rank-within-a-group","runs":[{"text":"Rank within a group"}]},{"t":"p","runs":[{"text":"Add a partition and the ranking restarts in each group:"}]},{"t":"code","lang":"sql","label":"SQL","text":"rank() OVER (PARTITION BY class_id ORDER BY score DESC)"},{"t":"p","runs":[{"text":"Now every class has its own first place. This is the pattern under every “best in category” question, and the next lesson turns it into a filter."}]},{"t":"h2","id":"the-check-that-catches-the-wrong-function","runs":[{"text":"The check that catches the wrong function"}]},{"t":"p","runs":[{"text":"If your ranking is used to pick rows, count the rows it picked. "},{"text":"rank() <= 3","code":true},{"text":" can return five rows when there are ties; "},{"text":"row_number() <= 3","code":true},{"text":" returns exactly three and may have excluded a tied row on the basis of nothing. Neither is wrong. One of them is what was asked for, and the count is how you find out which you built."}]}],"check":{"question":[{"text":"Five products have monthly sales of 500, 400, 400, 300 and 300. A report filters on "},{"text":"rank() OVER (ORDER BY sales DESC) <= 3","code":true},{"text":" to show the top three. How many rows does it return?"}],"options":[{"text":[{"text":"Exactly three, because rank stops at position 3 and the tied 300s are ranked 4 and 5."}],"correct":false,"tempting":[{"text":"The two 300s share rank 4 after the tied 400s took ranks 2 and 2, so neither is in the top three; this gets the count right by accident of the wrong mechanism and is still wrong about the 400s."}]},{"text":[{"text":"Five, because rank never skips positions, so the 300s are ranked 3 and the filter includes everything."}],"correct":false,"tempting":[{"text":"Describes dense_rank; rank skips position 3 entirely because two products occupy position 2."}]},{"text":[{"text":"Two, because rank assigns tied rows the same position and then discards all but one of them."}],"correct":false,"tempting":[{"text":"Rank never discards rows; every tied row keeps its shared position and is returned."}]},{"text":[{"text":"Three: the 500 at rank 1 and both 400s at rank 2, with the 300s at rank 4 and excluded."}],"correct":true,"tempting":null}],"order":[1,0,3,2],"why":null},"prev":{"slug":"what-a-window-is","title":"A window: GROUP BY that keeps the rows","minutes":9},"next":{"slug":"top-n-per-group","title":"Top N per group in two lines","minutes":8}}}