Re: sql question

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 10 Sep 2009 22:10:08 +0200
Message-ID: <4aa95d26$0$83241$e4fe514c_at_news.xs4all.nl>

amy schreef:
> 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.
>
>
>

Based on this example of only five records it is not possible to derive your business needs. If NAME is ascending with COLDATE within one ID (so a later coldate for the same ID will have a higher NAME) , it can be done with a simple max and group by:

select id, max(name),max(coldate) from test group by id;

If NAME is random, it's just a little bit more than that, but without a group by (see one of the other posts)

Shakespeare Received on Thu Sep 10 2009 - 15:10:08 CDT