Re: ranking style query

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 23 May 2002 22:06:29 -0500
Message-ID: <u8z6ankgz.fsf@rcn.com>

On 22 May 2002, ben_at_intrinsica.co.uk wrote:

> so I would like to get out:
>
> ID1, ID2, PlannedDate
> ---------------------
> a1, e3, 13/12/2002
> a2, e4, 14/12/2002

    SQL>select * from tst_tbl;

    ID ID PLANNEDDATE ACTUALDATE

• -- -------------------- -------------------- a1 e1 10-DEC-2002 12:00:00 10-DEC-2002 12:00:00 a1 e2 11-DEC-2002 12:00:00 11-DEC-2002 12:00:00 a1 e3 13-DEC-2002 12:00:00 a1 e4 15-DEC-2002 12:00:00 a1 e5 18-DEC-2002 12:00:00 a1 e6 21-DEC-2002 12:00:00 a2 e1 10-DEC-2002 12:00:00 10-DEC-2002 12:00:00 a2 e2 11-DEC-2002 12:00:00 11-DEC-2002 12:00:00 a2 e3 14-DEC-2002 12:00:00 14-DEC-2002 12:00:00 a2 e4 19-DEC-2002 12:00:00

    10 rows selected.

    SQL>select * from tst_tbl t1 where ACTUALDATE IS NULL

         and PLANNEDDATE = (select min (PLANNEDDATE)
                           from tst_tbl t2
                           where t1.id1 = t2.id1 and ACTUALDATE IS NULL);

    ID ID PLANNEDDATE	   ACTUALDATE
    -- -- -------------------- --------------------

    a1 e3 13-DEC-2002 12:00:00
    a2 e4 19-DEC-2002 12:00:00

From your description, I think the a2,e4 row needs the 19th?

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Thu May 23 2002 - 22:06:29 CDT