Re: SQL Query Help

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 13 Jun 2003 22:07:07 -0700
Message-ID: <F001.005B1B98.20030613214918@fatcity.com>

select <columns> from <table> A)
where <predicates>
and datecreated = (select min(datecreated)

from <table> b where b.cid = a.cid and b.pid = a.pid) At 08:14 PM 6/13/2003 -0800, you wrote:
I have a table with records like this

      CID S TO_CHAR(DATECREATED, MESSAGE                     PID


--------- - -------------------- -------------------- ----------

        2 N 01-feb-1974 19:45:45 service change                1        *
        3 N 01-feb-1974 19:45:45 service 
change                1        *
        3 N 01-feb-1974 21:45:45 service change                1
        1 N 01-jan-1974 12:34:45 msisdn change                 1        *
        1 N 01-jan-1974 19:45:45 service change                1
        2 N 01-jan-1974 19:45:45 service change                1
        1 N 01-nov-1974 17:45:45 service change                1
        1 N 01-nov-1974 19:45:45 service change                1

I want to display only the records with the *(not a value stored in the database.just used as a marker here).
i.e the records which meet the following.

1.earliest date
2.if there are multiple occurances of records with the same cid and pid combination i want only the record for
the combination of cid-pid and with the most earliest record(oldest time stamp).

i want to achieve this

      CID S TO_CHAR(DATECREATED, MESSAGE                     PID


--------- - -------------------- -------------------- ----------

        2 N 01-feb-1974 19:45:45 service change                1        *
        3 N 01-feb-1974 19:45:45 service 
change                1        *
        1 N 01-jan-1974 12:34:45 msisdn change                 1        *

I need some help in getting the query that can get the results like that.

Thanks
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Wolfgang Breitling
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message