For each unique value of some groupid column, how do I get rows with last 3 dates?

Question

I have a table with columns: FILING_ID, DATE, and BLAH

I'm trying to write a query that for each FILING_ID, returns the rows with the last three dates. If table is:

   FILING_ID   DATE    
     aksjdfj   2/1/2006
           b   2/1/2006
           b   3/1/2006
           b   4/1/2006
           b   5/1/2006

I would like:

   FILING_ID   DATE    
     aksjdfj   2/1/2006
           b   3/1/2006
           b   4/1/2006
           b   5/1/2006

I was thinking of maybe running some query to figure out the 3rd highest date for each FILING_ID then doing a join and comparing the cutoff date with the DATE?

I use PostgreSQL. Is there some way to use limit?

Answer 1

SELECT filing_id, date  -- more columns?
FROM  (
   SELECT *, row_number() OVER (PARTITION BY filing_id ORDER BY date DESC NULLS LAST) AS rn
   FROM   tbl
   ) sub
WHERE  rn < 4
ORDER  BY filing_id, date;  -- optionally order rows

NULLS LAST is only relevant if date can actually be NULL.
If date is not unique, you may need to break ties to get stable results.

• PostgreSQL sort by datetime asc, null first?
• Select first row in each GROUP BY group?

Is there some way to use limit?

Maybe. If you have an additional table holding all distinct filing_id (and possibly a few more, which are removed by the join), you can use CROSS JOIN LATERAL (, LATERAL is short syntax):

SELECT f.filing_id, t.*
FROM   filing f  -- table with distinct filing_id
     , LATERAL (
   SELECT date -- more columns?
   FROM   tbl
   WHERE  filing_id = f.filing_id
   ORDER  BY date DESC NULLS LAST
   LIMIT  3  -- now you can use LIMIT
   ) t
ORDER  BY f.filing_id, t.date;

• What is the difference between LATERAL and a subquery in PostgreSQL?

If you don't have a filing table, you can create one. Or derive it on the fly:

• Optimize GROUP BY query to retrieve latest record per user