SQL Limit Results Per Unique Value In Column

No Such Agency 0
Viewed 19

Here is dumbed down version of what I have.

I have a table called reports. It has the following columns:

  • id
  • type
  • created_timestamp

For type let's say I have 10 report types and several hundred reports of each type. How do I return the last 10 most recent reports of each type.

So the result should look something like this:

  • Last 10 type 1 report rows
  • Last 10 type 2 report rows
  • Last 10 type 3 report rows

etc.

aggregate group-by sql postgresql
1 Answers

You can use the window function ROW_NUMBER for example, but they are slow when you have a lot of rows per tspe

WITH CTE as
(SELECT
    id,
    type,
    created_timestamp,
    ROW_NUMBER() OVER(PARTITION BY type ORDER BY created_timestamp DESC) rn
FROM your_table)
SELECT id,type, created_timestamp FROM CTE WHERE rn < 11
nbk0
Related