Re: ranking style query

Hi,

Thank you both very much for your help :) Sorry Galen if you took offense, I am just not very good at SQL and spent hours trying to get a solution - hence why I came to the news group.

Thanks,

Ben

mark.powell_at_eds.com (Mark D Powell) wrote in message news:<178d2795.0205240545.3dabe79e_at_posting.google.com>...
> 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 Mon May 27 2002 - 08:48:47 CDT