SQL Limit Results Per Unique Value In Column-CodePudding

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.

CodePudding user response：

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