sql help

From: Sweetser, Joe <JSweetser_at_icat.com>
Date: Wed, 21 Dec 2011 18:38:34 +0000
Message-ID: <D18D6513433DF04394041EA42B53E91C58332AFB_at_ICATEXCH1.ICAT.com>

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));

insert into joe values (61061, 'bill_at_x.com', 'fred_at_y.com', to_date('07-May-2009 01:00:00', 'DD-MON-YYYY HH24:MI:SS'), 'request', 'Message1');
insert into joe values (61061, 'bill_at_x.com', 'nelson_at_y.com', to_date('08-May-2009 02:00:00', 'DD-MON-YYYY HH24:MI:SS'), 'request', 'Message1');
insert into joe values (160172, 'sam_at_x.com', 'bill_at_x.com', to_date('25-Sep-2009 06:35:00', 'DD-MON-YYYY HH24:MI:SS'), 'request', 'Message2');
insert into joe values (160172, 'bill_at_x.com', 'meywr_at_y.com', to_date('25-Sep-2009 07:44:00', 'DD-MON-YYYY HH24:MI:SS'), 'request', 'Message2');
insert into joe values (818035, 'blah_at_z.com', 'bill_at_x.com', to_date('31-Aug-2011 02:22:00', 'DD-MON-YYYY HH24:MI:SS'), 'request', 'Message3');
insert into joe values (818035, 'bill_at_x.com', 'kilburn_at_y.com', to_date('31-Aug-2011 02:32:00', 'DD-MON-YYYY HH24:MI:SS'), 'request', 'Message3');
insert into joe values (824115, 'nobody_at_z.com', 'bill_at_x.com', to_date('12-Sep-2011 11:40:00', 'DD-MON-YYYY HH24:MI:SS'), 'request', 'Message4');
insert into joe values (824115, 'bill_at_x.com', 'kilburn_at_y.com', to_date('13-Sep-2011 01:31:00', 'DD-MON-YYYY HH24:MI:SS'), 'request', 'Message4');

select * from joe;

1. 61061 bill_at_x.com fred_at_y.com 07-MAY-09 01.00.00.000000000 AM request Message1
2. 61061 bill_at_x.com nelson_at_y.com 08-MAY-09 02.00.00.000000000 AM request Message1
3. 160172 sam_at_x.com bill_at_x.com 25-SEP-09 06.35.00.000000000 AM request Message2
4. 160172 bill_at_x.com meywr_at_y.com 25-SEP-09 07.44.00.000000000 AM request Message2
5. 818035 blah_at_z.com bill_at_x.com 31-AUG-11 02.22.00.000000000 AM request Message3
6. 818035 bill_at_x.com kilburn_at_y.com 31-AUG-11 02.32.00.000000000 AM request Message3
7. 824115 nobody_at_z.com bill_at_x.com 12-SEP-11 11.40.00.000000000 AM request Message4
8. 824115 bill_at_x.com kilburn_at_y.com 13-SEP-11 01.31.00.000000000 AM request Message4

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
Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT Managers, LLC, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you.

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

Received on Wed Dec 21 2011 - 12:38:34 CST