Re: Groups of Groups?

Well, this is one of the cases where you need to select the same things over and over. Build the subselects step by step. I won't give you the complete select, I am just too tired ;) but I hope to give you a hint how to do it. First of all I modified your example a little bit. I suppose you don't want to get the records with the best matching phone numbers just to find out later that none of their date ranges is appropriate. Let's see:

(0) complete table

ABC 12345            23-MAR-1989 06-JUN-2099          36
ABC 12345            01-JAN-2003 06-JUN-2099           0
ABC 123450           01-JAN-2003 06-JUN-2099           1
ABC 123450           10-JAN-2003 06-JUN-2099         234
ABC 123451           ...
ABC 123452           ...
ABC 1234594          ...

(1) Get all records matching COMPANY_ID, PHONE_NUMBER_PREFIX and range
of START_DATE and END_DATE.

ABC 12345            01-JAN-2003 06-JUN-2099           0
ABC 123450           01-JAN-2003 06-JUN-2099           1
ABC 123450           10-JAN-2003 06-JUN-2099         234

select1 - you can figure out how to write it

(2) Of these get all records with highest phone prefix accuracy.

ABC 123450           01-JAN-2003 06-JUN-2099           1
ABC 123450           10-JAN-2003 06-JUN-2099         234

select2:
  select id, startdate from
   (select1) sub_1,
   (select maxphone from (select1)) sub_2   where sub_1.phone = sub_2.maxphone

(Notice you must insert the select1 two times).

(3) Of these get the one with most recent start date.
ABC 123450 10-JAN-2003 06-JUN-2099 234 select3:
  select ... from
   (select2) sub_a,
   select maxdate from (select2) sub_b
  where sub_a.date = sub_b.maxdate

(Again you will have to insert your select2 two times, thus select1
exists then four times in the complete select).

I hope this helps a little. Received on Thu Feb 06 2003 - 11:27:22 CST