PostgreSQL: Get the second to last MAX(date)

Question

I have two views in PostgreSQL. One to get the most recent total amounts of each organization. The other view is to get the second to last most recent total amounts of each organization and here is the problem: How to get the second to last MAX(date) in PostgreSQL? This is my code (note that 'date' is one of my columns, not the function):

CREATE VIEW vw_totaaldossiers AS
SELECT SUM(aantal) as totaal
FROM _dossier i1
WHERE date = (
    SELECT MAX(date) 
    FROM _dossier i2 
    WHERE i2.instantie = i1.instantie 
    GROUP BY i2.instantie
);

CREATE VIEW v2_relatiefdossiers AS
SELECT SUM(aantal) as relatief
FROM _dossier i3
WHERE date = (
    SELECT /* Here comes the second to last MAX(date) */ 
    FROM _dossier i4
    WHERE i4.instantie = i3.instantie
    GROUP BY i4.instantie
);

Thanks for the help!

score 8 · Answer 1 · answered May 15 '13 at 14:39

8

SELECT date 
FROM _dossier i2 
WHERE i2.instantie = i1.instantie 
GROUP BY i2.instantie
ORDER BY date DESC LIMIT 1 OFFSET 1

answered May 15 '13 at 14:39

Wiktor

2,909
4
17
23

Make sure _dossier.date has an index. – Andrew Lazarus May 15 '13 at 14:45
Thank you so much. This works. Have been searching over the internet like 3 hours. ;) – user2213250 May 15 '13 at 14:49

score 2 · Accepted Answer · answered May 15 '13 at 14:40

Something like this:

SELECT SUM(aantal) as relatief
FROM _dossier i3
WHERE date = (
    SELECT date 
    from (
       select date, 
              dense_rank() over (partition by i4.instantie order by date desc) as rnk
       FROM _dossier i4
       WHERE i4.instantie = i3.instantie
    ) t 
    where rnk = 2;
);

I'm not entirely sure, that i3 is actually visible inside the nested derived table!

You will need to run an explain to find out if the LIMI/OFFSET query from Wiktor is faster or slower.

PostgreSQL: Get the second to last MAX(date)

2 Answers2