{"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":"top-n-per-group","title":"Top N per group in two lines","url":"https://addaly.com/learn/data-and-sql/top-n-per-group","position":36,"of":66,"minutes":8,"answer":"Top N per group is a ranking window in a CTE and a filter on the rank outside it; the function you rank with is a statement about ties.","keyIdea":"Top N per group is a ranking window in a CTE and a filter on the rank outside it; the function you rank with is a statement about ties, and the ORDER BY inside the window must end in a column that cannot tie."},"body":{"format":"markdown","text":"## The question that used to be hard\n\n\"The three best-selling products in each category.\" Before window functions this took a correlated subquery that counted how many products in the same category outsold this one, and it ran once per row. With a window it is a rank and a filter:\n\n```sql\nWITH ranked AS (\n  SELECT category, product_id, sales,\n         row_number() OVER (PARTITION BY category\n                            ORDER BY sales DESC, product_id) AS rn\n  FROM product_sales\n)\nSELECT category, product_id, sales\nFROM ranked\nWHERE rn <= 3\nORDER BY category, rn;\n```\n\nRead the window aloud: within each category, order by sales descending and by product id to break ties, and number the rows from 1. Then keep the first three of each. The previous lesson explained why the filter has to live in a second layer, and why `product_id` is in the ORDER BY.\n\n## Which N, and which ties\n\n`row_number` returns exactly three per category, and when two products tie for third, one is dropped by the tiebreaker. That is right for \"show me three\". It is wrong for \"show me everything that made the top three\", where `dense_rank() <= 3` returns both tied products, and possibly four or five rows for that category. `rank() <= 3` is the third option: it returns the tied rows but, if two products tie for first, it returns only ranks 1, 1 and 3. Decide which the reader wants before choosing the function, and say which you chose in the column name or a comment.\n\n## The one-row case\n\nWhen N is 1 the previous module's `DISTINCT ON`, `arg_max` and join-back patterns all compete with this one. The window version is the most portable and the only one that extends to N of 2 without rewriting, so it is the default; `DISTINCT ON` is worth keeping for Postgres-only code where it reads more clearly.\n\n## Percent of the group, not a fixed count\n\nSometimes the ask is \"the top 10% of customers in each region\". `ntile(10)` gets close but produces equal-count buckets, which is what \"top 10% by count\" means. For \"customers who together make up the top 10% of revenue\" you want a running share:\n\n```sql\nWITH s AS (\n  SELECT region, customer_id, revenue,\n         sum(revenue) OVER (PARTITION BY region ORDER BY revenue DESC)\n           / sum(revenue) OVER (PARTITION BY region) AS cum_share\n  FROM customer_revenue\n)\nSELECT * FROM s WHERE cum_share <= 0.10;\n```\n\nTwo windows with the same partition, one ordered and one not, and the division gives each customer's position in the cumulative curve. The engine sorts once for both because the partition matches.\n\n## Performance, briefly\n\nThe window has to sort each category's rows by sales. For a product table that is nothing. For \"the latest three events per device\" over a billion-row events table it is the whole cost, and there are two things that help. An index on `(device_id, event_time DESC)` lets the engine read rows already in window order and skip the sort. And Postgres from version 15 recognises a `row_number() <= N` filter on the outer query and stops scanning each partition after N rows, so the query no longer numbers a million events per device to keep three. On older versions, and on engines without that optimisation, a `LATERAL` join that fetches `LIMIT 3` per device from the index is faster, at the cost of reading less naturally.\n\n## The trap in the tiebreaker\n\nIf the ORDER BY inside the window is not complete, the rows that make it into the top N can change between runs, and a daily report will show a different \"third-best product\" on days when nothing changed. The fix is the same as before: end the window's ORDER BY with a column that cannot tie. If the report's audience will notice a product appearing and disappearing, this is not a nicety.\n\n## The habit\n\nEvery top-N query should be able to say, in one sentence, what happens to ties, and that sentence should match the function used. \"Three per category, ties broken by product id\" is `row_number`. \"Everything in the top three positions\" is `dense_rank`. If you cannot say the sentence, the report is going to surprise somebody."},"check":{"question":"A daily report uses `row_number() OVER (PARTITION BY category ORDER BY sales DESC) <= 3` and, on a day when no sales changed, shows a different third product in one category than it did the day before. What is the most likely cause?","options":[{"text":"The window recomputed ranks from a different starting row because the table was vacuumed overnight and its physical order changed the ranking.","correct":false,"tempting":"Physical order affects which tied row the engine happens to see first, but that only matters because the ORDER BY allows the tie; the cause is the incomplete ordering, not the vacuum."},{"text":"row_number is non-deterministic by design and cannot be used for reports; rank should be used instead.","correct":false,"tempting":"row_number is deterministic once the ORDER BY is complete; switching to rank changes tie handling rather than fixing the ordering."},{"text":"Two products tie on sales in that category and the window's ORDER BY has no tiebreaker, so which one gets number 3 is arbitrary.","correct":true},{"text":"The CTE was materialised on one day and inlined on the next, and the two execution paths order partitions differently.","correct":false,"tempting":"Materialisation changes how a CTE is executed but never which row satisfies an ORDER BY; an incomplete ordering is arbitrary under either path."}]},"prev":{"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"},"next":{"slug":"running-totals-and-frames","title":"Running totals, and the frame you did not know you set","url":"https://addaly.com/learn/data-and-sql/running-totals-and-frames"},"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) 'Top N per group in two lines', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/top-n-per-group (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":"Top N per group in two lines","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"Top N per group is a ranking window in a CTE and a filter on the rank outside it; the function you rank with is a statement about ties, and the ORDER BY inside the window must end in a column that cannot tie.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-question-that-used-to-be-hard","runs":[{"text":"The question that used to be hard"}]},{"t":"p","runs":[{"text":"“The three best-selling products in each category.” Before window functions this took a correlated subquery that counted how many products in the same category outsold this one, and it ran once per row. With a window it is a rank and a filter:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH ranked AS (\n  SELECT category, product_id, sales,\n         row_number() OVER (PARTITION BY category\n                            ORDER BY sales DESC, product_id) AS rn\n  FROM product_sales\n)\nSELECT category, product_id, sales\nFROM ranked\nWHERE rn <= 3\nORDER BY category, rn;"},{"t":"p","runs":[{"text":"Read the window aloud: within each category, order by sales descending and by product id to break ties, and number the rows from 1. Then keep the first three of each. The previous lesson explained why the filter has to live in a second layer, and why "},{"text":"product_id","code":true},{"text":" is in the ORDER BY."}]},{"t":"h2","id":"which-n-and-which-ties","runs":[{"text":"Which N, and which ties"}]},{"t":"p","runs":[{"text":"row_number","code":true},{"text":" returns exactly three per category, and when two products tie for third, one is dropped by the tiebreaker. That is right for “show me three”. It is wrong for “show me everything that made the top three”, where "},{"text":"dense_rank() <= 3","code":true},{"text":" returns both tied products, and possibly four or five rows for that category. "},{"text":"rank() <= 3","code":true},{"text":" is the third option: it returns the tied rows but, if two products tie for first, it returns only ranks 1, 1 and 3. Decide which the reader wants before choosing the function, and say which you chose in the column name or a comment."}]},{"t":"h2","id":"the-one-row-case","runs":[{"text":"The one-row case"}]},{"t":"p","runs":[{"text":"When N is 1 the previous module’s "},{"text":"DISTINCT ON","code":true},{"text":", "},{"text":"arg_max","code":true},{"text":" and join-back patterns all compete with this one. The window version is the most portable and the only one that extends to N of 2 without rewriting, so it is the default; "},{"text":"DISTINCT ON","code":true},{"text":" is worth keeping for Postgres-only code where it reads more clearly."}]},{"t":"h2","id":"percent-of-the-group-not-a-fixed-count","runs":[{"text":"Percent of the group, not a fixed count"}]},{"t":"p","runs":[{"text":"Sometimes the ask is “the top 10% of customers in each region”. "},{"text":"ntile(10)","code":true},{"text":" gets close but produces equal-count buckets, which is what “top 10% by count” means. For “customers who together make up the top 10% of revenue” you want a running share:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH s AS (\n  SELECT region, customer_id, revenue,\n         sum(revenue) OVER (PARTITION BY region ORDER BY revenue DESC)\n           / sum(revenue) OVER (PARTITION BY region) AS cum_share\n  FROM customer_revenue\n)\nSELECT * FROM s WHERE cum_share <= 0.10;"},{"t":"p","runs":[{"text":"Two windows with the same partition, one ordered and one not, and the division gives each customer’s position in the cumulative curve. The engine sorts once for both because the partition matches."}]},{"t":"h2","id":"performance-briefly","runs":[{"text":"Performance, briefly"}]},{"t":"p","runs":[{"text":"The window has to sort each category’s rows by sales. For a product table that is nothing. For “the latest three events per device” over a billion-row events table it is the whole cost, and there are two things that help. An index on "},{"text":"(device_id, event_time DESC)","code":true},{"text":" lets the engine read rows already in window order and skip the sort. And Postgres from version 15 recognises a "},{"text":"row_number() <= N","code":true},{"text":" filter on the outer query and stops scanning each partition after N rows, so the query no longer numbers a million events per device to keep three. On older versions, and on engines without that optimisation, a "},{"text":"LATERAL","code":true},{"text":" join that fetches "},{"text":"LIMIT 3","code":true},{"text":" per device from the index is faster, at the cost of reading less naturally."}]},{"t":"h2","id":"the-trap-in-the-tiebreaker","runs":[{"text":"The trap in the tiebreaker"}]},{"t":"p","runs":[{"text":"If the ORDER BY inside the window is not complete, the rows that make it into the top N can change between runs, and a daily report will show a different “third-best product” on days when nothing changed. The fix is the same as before: end the window’s ORDER BY with a column that cannot tie. If the report’s audience will notice a product appearing and disappearing, this is not a nicety."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Every top-N query should be able to say, in one sentence, what happens to ties, and that sentence should match the function used. “Three per category, ties broken by product id” is "},{"text":"row_number","code":true},{"text":". “Everything in the top three positions” is "},{"text":"dense_rank","code":true},{"text":". If you cannot say the sentence, the report is going to surprise somebody."}]}],"check":{"question":[{"text":"A daily report uses "},{"text":"row_number() OVER (PARTITION BY category ORDER BY sales DESC) <= 3","code":true},{"text":" and, on a day when no sales changed, shows a different third product in one category than it did the day before. What is the most likely cause?"}],"options":[{"text":[{"text":"The window recomputed ranks from a different starting row because the table was vacuumed overnight and its physical order changed the ranking."}],"correct":false,"tempting":[{"text":"Physical order affects which tied row the engine happens to see first, but that only matters because the ORDER BY allows the tie; the cause is the incomplete ordering, not the vacuum."}]},{"text":[{"text":"row_number is non-deterministic by design and cannot be used for reports; rank should be used instead."}],"correct":false,"tempting":[{"text":"row_number is deterministic once the ORDER BY is complete; switching to rank changes tie handling rather than fixing the ordering."}]},{"text":[{"text":"Two products tie on sales in that category and the window’s ORDER BY has no tiebreaker, so which one gets number 3 is arbitrary."}],"correct":true,"tempting":null},{"text":[{"text":"The CTE was materialised on one day and inlined on the next, and the two execution paths order partitions differently."}],"correct":false,"tempting":[{"text":"Materialisation changes how a CTE is executed but never which row satisfies an ORDER BY; an incomplete ordering is arbitrary under either path."}]}],"order":[0,3,2,1],"why":null},"prev":{"slug":"ranking-functions","title":"ROW_NUMBER, RANK and DENSE_RANK: three answers to “what position”","minutes":8},"next":{"slug":"running-totals-and-frames","title":"Running totals, and the frame you did not know you set","minutes":9}}}