Re: Groups of Groups?

i think this works:

select * from
(select * from blah
where instr(replace('123-450-1234', '-', ''), phone_number_prefix) > 0 and to_date('01-feb-2003', 'DD-MM-YYYY') between start_date and end_date order by lpad(phone_number_prefix, 10, '0') ||

         to_char(start_date, 'YYYYMMDD') desc ) where rownum = 1

this gives 'preference' to the longest matching prefix over the later start_dates.

there are probably slicker (and more readable) ways to do it, too.

j6vflbl6vy6g8o001_at_sneakemail.com (Kevin Arthur) wrote in message news:<e87297f1.0302052213.4bbd8b4f_at_posting.google.com>...
> I've got a table that looks like this:
>
> COMPANY_ID NOT NULL CHAR(3)
> PHONE_NUMBER_PREFIX NOT NULL VARCHAR2(16)
> START_DATE NOT NULL DATE
> END_DATE NOT NULL DATE
> LOCATION_ID NOT NULL NUMBER(8)
> [...]
>
> Table can contain data like this:
>
> COM PHONE_NUMBER_PRE START_DATE END_DATE LOCATION_ID
> --- ---------------- ----------- ----------- -----------
> ABC 12345 23-MAR-1989 06-JUN-2099 36
> ABC 12345 11-NOV-1994 06-JUN-2099 0
> ABC 123450 18-FEB-1986 06-JUN-2099 1
> ABC 123450 01-JAN-2003 06-JUN-2099 234
> ABC 123451 ...
> ABC 123452 ...
> ABC 1234594 ...
>
>
> Note multiple prefixes for multiple date ranges.
>
> Given a particular phone number, I need it to match on the longest
> matching prefix available. Note that every possible pattern is not
> present; only specific ones.
>
> I also needs to get the record with the latest date range for that
> particular prefix, where the date of the call to that number fits
> within that date range.
>
> What I'm after is the "location_id" which will be different for each
> entry.
>
> So in the above example, phone number "123-450-1234" with a call date
> of February 1, 2003 should match on:
>
> ABC 123450 01-JAN-2003 06-JUN-2099 234
>
> since it is a more specific match than just '12345'.
>
> I'm afraid I'm going in circles. I can do (an admittedly not pretty)
> query using max() to group on "phone_number_prefix" and get the
> 'largest' prefix.
>
> Or, I can do same on "start_date" go get the latest start date.
>
> But, how to the the combination of both?
>
> If you do both max group functions in one query, you combine values
> from multiple rows. Not the desired result.
>
> I am attempting to use just straight SQL. Maybe a PL/SQL block is the
> way to go, but I was just sure I could do it through regular SQL.
> (Stubborn, I guess)
>
> I feel like I need a series of nested sub-queries, but not sure how to
> go about it, since one I group things (prefix or date), I have nothing
> left in common.
>
> Any help would be much appreciated!
>
>
> For what it's worth, here's a query re getting the max prefix:
>
> select
> o.phone_number,
> max(p.phone_number_prefix)
> -- max(p.start_date) --no,no,no!
> from
> originating_table o,
> phone_prefix_table p
> where
> (p.company_id = o.company_id)
> AND (substr(o.phone_number,1,length(p.phone_number_prefix))
> = p.phone_number_prefix)
> AND (o.call_date BETWEEN p.start_date AND p.end_date)
> group by o.phone_number;
>
>
> Thanks!
>
> Kevin Arthur
Received on Thu Feb 06 2003 - 11:54:31 CST