1

So I found this similar question and answer at (Find max value and show corresponding value from different field in SQL server) but I want to take it one step further. I want to get the latest date for each ID and the corresponding TYPE instead of just the absolute max for all entries. Any suggestions?

ID      Type        Date
1       Initial      1/5/15 
1       Periodic     3/5/15
2       Initial      2/5/15  
3       Initial      1/10/15
3       Periodic     3/6/15  
4       Initial      3/8/15

The code below shows how to get just the max date of ALL entries, but I want the max date for each ID and then the corresponding Type.

select id, type, date
from yourtable
where date in (select max(date)
                     from yourtable)

OR

select id, type, date
from yourtable t1
inner join
(
  select max(date) maxdate
  from yourtable
) t2
  on t1.date = t2.maxdate;
Community
  • 1
  • 1
AccessProgrammer
  • 11
  • 1
  • 5

1 Answers1

2

You can use the first method with a correlated subquery:

select id, type, date
from yourtable as t
where date in (select max(date)
               from yourtable as t2
               where t2.id = t.id);

Or, group by id in the second:

select t1.id, t1.type, t1.date
from yourtable as t1 inner join
     (select id, max(date) maxdate
      from yourtable
      group by id
     ) t2
     on t1.date = t2.maxdate and t1.id = t2.id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786