SQL Query Help

From: Basavaraja, Ravindra <Ravindra.Basavaraja_at_T-Mobile.com>
Date: Fri, 13 Jun 2003 20:32:58 -0700
Message-ID: <F001.005B1B77.20030613201437@fatcity.com>

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). Received on Fri Jun 13 2003 - 22:32:58 CDT