Re: ranking style query

Hi Galen,

Thanks for your reply. Slight problem with the solution (my fault for not specifiying the data correctly) - The planned date is not unique for an ID1, ID2 pair:

create table tst_tbl (

	   ID1 Number(11) NOT NULL,
	   ID2 Number(11) NOT NULL,
	   PlannedDate Date,
	   ActualDate Date

INSERT INTO TST_TBL Values ('a1', 'e1', '10-DEC-2002', '10-DEC-2002');
INSERT INTO TST_TBL Values ('a1', 'e2', '11-DEC-2002', '11-DEC-2002');
INSERT INTO TST_TBL Values('a1', 'e3', '13-DEC-2002', NULL);
INSERT INTO TST_TBL Values('a1', 'e4', '15-DEC-2002', NULL);
INSERT INTO TST_TBL Values('a1', 'e5', '18-DEC-2002', NULL);
INSERT INTO TST_TBL Values('a1', 'e6', '21-DEC-2002', NULL);
INSERT INTO TST_TBL Values('a1', 'e7', '21-DEC-2002', NULL);
INSERT INTO TST_TBL Values('a1', 'e8', '21-DEC-2002', NULL);
INSERT INTO TST_TBL Values('a2', 'e1', '10-DEC-2002', '10-DEC-2002');
INSERT INTO TST_TBL Values('a2', 'e2', '11-DEC-2002', '11-DEC-2002');
INSERT INTO TST_TBL Values('a2', 'e3', '14-DEC-2002', '14-DEC-2002');
INSERT INTO TST_TBL Values ('a2', 'e4', '19-DEC-2002', NULL);

So running your solution would bring back more then one entry for a1 etc.

Any other ideas?

Thanks,

Ben.

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u8z6ankgz.fsf_at_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?
Received on Fri May 24 2002 - 05:36:02 CDT