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

Answer 1

Hmm, looking at Grouped LIMIT in PostgreSQL: show the first N rows for each group?, I came up with:

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