Re: ranking style query

ben_at_intrinsica.co.uk (ben) wrote in message news:<5bee31fb.0205240236.5bc6f27_at_posting.google.com>...
> 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?

If I understand your problem with the presented solution then since the planned date for the id1, id2 pair is not unique but you only want to see the minimum planned date I think you can just add "and rownum = 1" to the subquery to find the min(planneddate) to get back just one row and prevent an error when multiple rows exist.

HTH -- Mark D Powell -- Received on Fri May 24 2002 - 08:45:44 CDT