Re: query issue

From: kibeha <kibeha_at_post6.tele.dk>
Date: 5 Feb 2004 06:06:23 -0800
Message-ID: <444b180d.0402050606.1be2ceb_at_posting.google.com>

> select * from numbers where n_id in
> (select max(n_id) from numbers group by n_number);
>
>
> in your opinion is there any way to avoid the nested query?
> jc

In Oracle 9 there is a way :

select

   n_number,
   max(fk1) keep (dense_rank last order by n_id) last_fk1 from numbers
group by n_number;

What this means is, that it will only do a max(fk1) on those records with the last n_id value within each group. As n_id is unique, the max(fk1) will only operate on the one record within each n_number group that has the largest n_id.

In Oracle 8 you won't avoid the nested query, but you can avoid accessing numbers table twice :

select distinct n_number, last_fk1
from
(

   select

      n_number,
      last_value(fk1) over (partition by n_number order by n_id rows

between unbounded preceding and unbounded following) last_fk1

   from numbers
);

The analytic function will give you the fk1 value for the last n_id within each n_number partition as you go along, but as that inner select will return a row for each row in numbers, you select distinct to only get the relevant information.

Hope these may help.

KiBeHa Received on Thu Feb 05 2004 - 15:06:23 CET