record_id Status Due_date
549 In Progress 2004-06-02 00:00:00.000
549 Not Started 2004-06-07 00:00:00.000
549 Not Started 2004-06-08 00:00:00.000
549 Waiting 2004-05-31 00:00:00.000
549 Waiting 2004-06-04 00:00:00.000
550 Completed 2004-05-05 00:00:00.000
551 Completed 2004-05-06 00:00:00.000
551 Completed 2004-05-07 00:00:00.000
551 Completed 2004-05-10 00:00:00.000
551 Not Started 1900-01-01 00:00:00.000
552 Not Started 1900-01-01 00:00:00.000
Hi I have this table with 3 columns.. What I want is
Distinct(record_id),max(due_date) and Status.. I tried this
select distinct(record_id),status,(due_date) from table1 where
(due_date) in
(select max(due_date) from table1 as A where a.record_id=record_id
and a.due_date is not null group by a.record_id,status)
So the result that I want is
Record Status Max(due_date)
549 Not Started 2004-06-09 00:00:00.000
550 Completed 2004-05-05 00:00:00.000
551 Completed 2004-05-10 00:00:00.000
Any help is appreciated..
Thanks
AJ