CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Stephen Smith
Stephen Smith

Reputation: 377

Select entry with second most recent date

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

Upvotes: 3

Views: 1012

Answers (2)

kevchoi
kevchoi

Reputation: 434

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;

Upvotes: 3

Ali Nikneshan
Ali Nikneshan

Reputation: 3502

you can use max instead of distinct,order by:

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

Upvotes: 0

Related Questions