CODEWITHSUNDEEP
sqlpostgresqlgroup-byaggregate
No Such Agency
No Such Agency

Reputation: 79

SQL Limit Results Per Unique Value In Column

Here is dumbed down version of what I have.

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

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:

etc.

Upvotes: 1

Views: 30

Answers (1)

nbk
nbk

Reputation: 49410

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

Upvotes: 1

Related Questions