Re: Groups of Groups?

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. Received on Fri Feb 07 2003 - 01:20:17 CST