Getting most recent date values where other column values change

Question

I have a table that is as follows:

  id value  date
  ---------------
  a, test,  01-01-15
  a, test,  01-02-15
  a, test,  01-03-15
  a, test1, 01-04-15
  a, test1, 01-05-15
  b, test, 01-01-15
  b, test, 01-02-15

I need to write a query to be able to grab the latest mapping for both a & b

for instance, I want my results to be as follows:

a, test1, 01-05-15
b, test,  01-02-15

I can't seem to wrap my head around how to accomplish this

select max(date)

only returns the latest date value

and select max(date), value group by value obviously does not return the id column

Which RDBMS you are using – Pரதீப் Aug 11 '15 at 15:30 — Pரதீப், Aug 11 '15 at 15:30
I need the ID and value columns in the results – Just_Some_Guy Aug 11 '15 at 15:31 — Just_Some_Guy, Aug 11 '15 at 15:31

score 1 · Answer 1 · answered Aug 11 '15 at 15:32

If your database supports Row_number then use this

select id,value,date from 
(
select row_number() over(partition by value order by date desc) as RN,
       id,value,date
from yourtable
) A
where RN=1

another way is by joining the result back to the table.

SELECT A.id, 
       A.value, 
       A.date 
FROM   yourtable A 
       INNER JOIN (SELECT Max(date) AS date, 
                          value 
                   FROM   yourtable 
                   GROUP  BY value) B 
               ON A.date = b.date 
                  AND A.value = B.value

score 1 · Accepted Answer · answered Aug 11 '15 at 15:34

Depending on what version of SQL you use, you may have a function called ROW_NUMBER() OVER() that can help.

WITH x AS
(SELECT id, value, date 
        , ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RowNum
FROM table
)
SELECT id, value, date
FROM x
WHERE RowNum = 1

The above would work in SQL Server and anything else that supports CTE and Windowing Functions. You could also write it as a derived table like this:

SELECT id, value, date FROM (SELECT id, value, date , ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RowNum FROM table ) AS x WHERE RowNum = 1

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

1

Simple

SELECT DISTINCT ON (id) *
FROM   tbl
ORDER  BY id, date DESC NULLS LAST;

Detailed explanation:

Select first row in each GROUP BY group?

Fast

For data distributions with many rows per id:

Optimize GROUP BY query to retrieve latest record per user

edited May 23 '17 at 12:30

Community

1
1

answered Aug 11 '15 at 15:40

Erwin Brandstetter

605,456
145
1,078
1,228

Getting most recent date values where other column values change

3 Answers3

Simple

Fast