0

I have a table containing, for example, this data:

id | value | name   | date
1  | 1     | 'one'  | 2015-01-02
2  | 1     | 'two'  | 2015-02-03
3  | 2     | 'three'| 2014-01-03
4  | 2     | 'four' | 2014-01-02

I want for each distinct value, the name of the row with the latest date. So:

value | name   | date
1     | 'two'  | 2015-02-03
2     | 'three'| 2014-01-03

I currently have this query: SELECT value, MAX(date) FROM table GROUP BY value, which gives me the value and date columns I'm looking for. How do I modify the query to add the name field? Simply adding it to the SELECT clause won't work, as Postgres will (understandably) complain I have to add it to the GROUP BY clause. But doing so will add it to the uniqueness check, and my query will return all 4 rows. All I need is the name of the row where it found the latest date.

Jorn
  • 20,612
  • 18
  • 79
  • 126
  • 1
    Possible duplicate of [Efficient latest record query with Postgresql](http://stackoverflow.com/questions/1684244/efficient-latest-record-query-with-postgresql) Several good examples already exist for this type of question. – xQbert Nov 17 '15 at 17:12
  • Hi Jorn. my answer worked for you? – Abhishek Ginani Nov 17 '15 at 18:22

2 Answers2

1

distinct on() is the most efficient way to do this with Postgres

select distinct on (value) id, value, name, date
from the_table
order by value, date;

SQLFiddle example: http://sqlfiddle.com/#!15/dff68/1

0

This will give you all required fields:

select t1.* from table t1 
inner join (
  SELECT value, MAX(date) as date FROM table GROUP BY value
 )t2 on t1.date=t2.date;

SQL Fiddle: http://sqlfiddle.com/#!15/9491f/2

Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35