Return value at max date for a particular id

Here’s my sql server table

ID       Date       Value 
___      ____       _____
3241     9/17/12    5
3241     9/16/12    100
3241     9/15/12    20
4355     9/16/12    12
4355     9/15/12    132
4355     9/14/12    4
1234     9/16/12    45
2236     9/15/12    128
2236     9/14/12    323
2002     9/17/12    45

This seems like it should be easy to do, but I don’t know why I’m stuck. I’d like to select ONLY the max(date) and value at that max(date) for each id. I want to ignore all other dates that aren’t the max(date) with respect to each id.

Here’s what I’d like the table to look like:

ID       Date       Value 
___      ____       _____
3241     9/17/12    5
4355     9/16/12    12
1234     9/16/12    45
2236     9/15/12    128
2002     9/17/12    45

I tried group by using max(date), but it didn’t group anything. I’m not sure what I’m doing wrong. Thanks in advance for the help!

Answer

You can use the following:

select t1.id, t2.mxdate, t1.value
from yourtable t1
inner join
(
  select max(date) mxdate, id
  from yourtable
  group by id
) t2
  on t1.id = t2.id
  and t1.date = t2.mxdate

See Demo

Advertisement

Answer