Quick SQL question

[Laeg]

Given

id | create_date | value
1 06/01/2009 12:36:35 a
2 06/01/2009 12:36:35 a
3 06/01/2009 12:36:35 a
4 07/01/2009 10:36:35 b
5 07/01/2009 10:36:35 b
6 07/01/2009 12:56:35 b
7 08/01/2009 1:56:35 a
8 08/01/2009 1:56:55 a
9 08/01/2009 1:57:00 a
10 09/01/2009 1:57:00 b

How would I return all results for each distinct value on the latest available date for that value?

So it would return

7 08/01/2009 1:56:35 a
8 08/01/2009 1:56:55 a
9 08/01/2009 1:57:00 a
10 09/01/2009 1:57:00 b

As 08/01/2009 is the max date for a and it has 3 corresponding records and 09/01/2009 is the max date for b
with 1 result

 

[IanWaterman]

Try

Select ID, Value, createdate
from table t
inner join
(select ID, Value, max(createdate) as CDate
from table
group by 1d, Value) d
on t.createdate = d.CDate

Ian

 

[JarlH]

Alternative solution:
[tt]
select * from thistable as tko
where cast(create_date as date) =
(select max(cast(create_date as date))
from thistable where val = tko.val)[/tt]

Since it seems like you're using SQL Server (or other DBMS without ANSI compliant date handling), I suppose you'll have to use something else than CAST to get extract the date part from the create_date column.)

 

[JarlH]

Just realized, switch CAST and MAX in the sub-select and save a few steps when excecuting.

I.e.[tt]
select * from thistable as tko
where cast(create_date as date) =
(select cast(max(create_date) as date)
from thistable where val = tko.val)
[/tt]