sql question

I don't seem to be able to figure this out using distinct, group by or
the having clause. Can someone help?

I have a table with the following records. If there are any duplicate
IDs, I would like to just list the record with the latest timestamp.

ID NAME COLDATE
---------- ----- --------------------
1 G 09-sep-2009 10:20:02
1 J 10-sep-2009 10:20:31
1 L 10-sep-2009 10:21:00
2 H 10-sep-2009 10:20:17
3 K 31-aug-2009 10:20:43


The result I'm expecting is

ID NAME COLDATE
---------- ----- --------------------
1 L 10-sep-2009 10:21:00
2 H 10-sep-2009 10:20:17
3 K 31-aug-2009 10:20:43


Can someone help? thanks in advance.

amy schrieb:
> I don't seem to be able to figure this out using distinct, group by or
> the having clause. Can someone help?
>
> I have a table with the following records. If there are any duplicate
> IDs, I would like to just list the record with the latest timestamp.
>
> ID NAME COLDATE
> ---------- ----- --------------------
> 1 G 09-sep-2009 10:20:02
> 1 J 10-sep-2009 10:20:31
> 1 L 10-sep-2009 10:21:00
> 2 H 10-sep-2009 10:20:17
> 3 K 31-aug-2009 10:20:43
>
>
> The result I'm expecting is
>
> ID NAME COLDATE
> ---------- ----- --------------------
> 1 L 10-sep-2009 10:21:00
> 2 H 10-sep-2009 10:20:17
> 3 K 31-aug-2009 10:20:43
>
>
> Can someone help? thanks in advance.
>
>
>

Aren't you supposed to do your homework yourself?


Lars

amy wrote:
> I don't seem to be able to figure this out using distinct, group by or
> the having clause. Can someone help?
>
> I have a table with the following records. If there are any duplicate
> IDs, I would like to just list the record with the latest timestamp.
>
> ID NAME COLDATE
> ---------- ----- --------------------
> 1 G 09-sep-2009 10:20:02
> 1 J 10-sep-2009 10:20:31
> 1 L 10-sep-2009 10:21:00
> 2 H 10-sep-2009 10:20:17
> 3 K 31-aug-2009 10:20:43
>
>
> The result I'm expecting is
>
> ID NAME COLDATE
> ---------- ----- --------------------
> 1 L 10-sep-2009 10:21:00
> 2 H 10-sep-2009 10:20:17
> 3 K 31-aug-2009 10:20:43
>
>
> Can someone help? thanks in advance.
>
>
>

select * from a_table a1 where not exists (select null from a_table a2 where a2.id = a1.id and
a2.coldate > a1.coldate) and 1 < (select count(*) from a_table a3 where a3.id = a1.id and
a3.coldate <= a1.coldate )

This may list the same id more than once if highest coldate is a duplicate too, but those can be
eliminated if so desired.

Lars Tetzlaff wrote:
>
> Aren't you supposed to do your homework yourself?
>
>
> Lars

It is quite intelligent to ask for help when one cannot solve a problem oneself.