Re: sql question
From: Tiago <diariodastrilhas_at_gmail.com>
Date: Thu, 10 Sep 2009 12:03:57 -0700 (PDT)
Message-ID: <d20839a7-2863-4dcf-a661-e40c97a63238_at_k33g2000yqa.googlegroups.com>
On Sep 10, 2:41 pm, amy <amykl..._at_gmail.com> 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.
Date: Thu, 10 Sep 2009 12:03:57 -0700 (PDT)
Message-ID: <d20839a7-2863-4dcf-a661-e40c97a63238_at_k33g2000yqa.googlegroups.com>
On Sep 10, 2:41 pm, amy <amykl..._at_gmail.com> 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 a.id, a.name, a.coldate
from mytable a
where a.coldate =
(select max(b.coldate) from mytable b
where a.id=b.id)
- T