Re: Groups of Groups?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 7 Feb 2003 19:46:23 GMT
Message-ID: <b212ef$17tg0d$1@ID-82536.news.dfncis.de>

> Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<b1u8s7$166n38$1_at_ID-82536.news.dfncis.de>...

>> select id, start_date from (
>>   select 
>>     id, 
>>     max(start_date) over() start_date, 
>>     rank() over (order by length(prefix) desc, start_date desc) r
>>   from 
>>     prefix_
>>   where
>>     sysdate between start_date and end_date and
>>     substr('1234501234',1,length(prefix)) = prefix
>>   )
>> where r = 1;

>
> Wow! I am working with Ansi-SQL89 still (Oracle 7) :( and didn't know
> of all the new functions that meanwhile exist. I have learned a great
> deal from your post. However shouldn't it be:
>
> select
> id,
> start_date, ...
>
> instead of
>
> select
> id,
> max(start_date) over() start_date, ...
>
> What would the max function be good for? Don't you already take the
> start_date into account in your ranking clause? With max you are
> taking the maximum of all start dates instead of taking the start date
> of the best record.

This is because it was not clear to me what you wanted. I thought you need to have both: the id of the longest matching substring AND the LATEST start_date of any prefix matching the phonenumber.

Rene Nyffenegger

-- 
  no sig today

Received on Fri Feb 07 2003 - 13:46:23 CST