Select entry with second most recent date

Question

So normally, if I wanted to take the most recent of each type in this table:

type |    date
-----------------
  A  | 2008-07-06
  B  | 2014-08-02
  A  | 2009-12-27
  A  | 2004-03-01
  B  | 2008-10-17
  B  | 2015-01-01

I'd do this:

SELECT DISTINCT ON (type) type, date
FROM t
ORDER BY type, date DESC;

But what if I want to get not the most recent, but the second most recent? So:

type |    date
-----------------
  A  | 2008-07-06
  B  | 2014-08-02

score 3 · Answer 1 · edited May 23 '17 at 12:31

3

SELECT * FROM (
  SELECT type, date, ROW_NUMBER()
  OVER (PARTITION BY type ORDER BY date DESC) AS row FROM t
  ) tmp
WHERE tmp.row=2;

edited May 23 '17 at 12:31

Community

answered Jan 31 '16 at 04:59

kevchoi

score 0 · Answer 2 · answered Jan 31 '16 at 04:55

0

you can use max instead of distinct,order by:

SELECT type,max(date) from t group by type

answered Jan 31 '16 at 04:55

Ali Nikneshan

There's other stuff in the row that I want. But in any case, how do I get not the max, but the next one down from the max – not the most recent date, but the second-to-most-recent one? – Stephen Smith Jan 31 '16 at 04:57
for other item, you can add them in group by list. for second you need nested query. `select * from (select * from order by .. desc limit 2) order by asc limit 1` – Ali Nikneshan Jan 31 '16 at 05:01

2 Answers2