Re: sql help

From: Igor Neyman <igor.neyman_at_gmail.com>
Date: Wed, 21 Dec 2011 13:55:19 -0500
Message-ID: <CAJoeKmsucAccUR3ke8zA8JmiD-f01pa=Etntz29-+C=7GNcF2w_at_mail.gmail.com>

Trying it the second time (first time didn't go through because of "over-quoting").
Is this what you are looking for:
WITH aggr AS (SELECT message, min(created_time) min_time FROM joe GROUP BY message)
SELECT j.*
FROM joe j, aggr
WHERE j.message = aggr.message
AND j.created_time = aggr.min_time;

?
Regards,
Igor Neyman

On Wed, Dec 21, 2011 at 1:38 PM, Sweetser, Joe <JSweetser_at_icat.com> wrote:

> I don't think this should be as hard as I am making it. I want to select
> the earlier of all records that have the same message. There is a bit of
> extraneous data in here but I'm lazy and it was easier to leave it in so my
> apologies for that. I hope this formats ok.
> CREATE TABLE "JOE" ( "ID_ACTIVITY" NUMBER(10),
> "CREATED_USERNAME" VARCHAR2(100),
> "OWNER_USERID" VARCHAR2(100),
> "CREATED_TIME" TIMESTAMP(6),
> "CATAGORY" VARCHAR2(8),
> "MESSAGE" VARCHAR2(2000));
>
>
> So, I am looking for a statement that would return rows 1, 3, 5, and 7. I
> have tried various things with min(created_time) but that is too limiting
> since I only get one row (duh).
>
> Any/all pointers/tips welcome.
>
> thanks,
> -joe
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 21 2011 - 12:55:19 CST