Something like SELECT DISTINCT ON, but for N > 1

Question

If I want to get the first row, or the most recent single row on a field in postgres, select distinct on seems great, see this answer.

DISTINCT ON is a syntax for wanting exactly 1 entry. But what if I want the N most recent entries? How would I transform this:

CREATE VIEW your_view AS
SELECT DISTINCT ON (id) *
FROM your_table a
ORDER BY id, date DESC;

But for

"Select the most recent n=2 entries per id" rather than "select the most recent n=1 entries per id?" ?

I assume it's an group by subquery, but I'm not quite seeing it.

score 1 · Accepted Answer · answered May 03 '20 at 06:47

For a greatest-n-per-group query with n > 1 you typically use a window function:

select *
from (
  select *, row_number() over (partition by id order by "date" desc" as rn
  from the_table
) x
where rn <= 2;

1 Answers1