RE: I still have trouble wrapping my head around these analytical functions (should be simple?)

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 30 Nov 2006 18:11:57 -0800
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9B2A@ALVMBXW05.prod.quest.corp>

 

Thank you to all who answered. the question was: rewrite this query using analytical functions select id, smallest, largest, rownum as rank from
(select id, min (value) as smallest, max (value) as largest  from zzz
 group by id
 order by 2, 3
) ;

and the suggested solutions were:

solution 1) with a sub-select (Charles Schultz, Jared Still, Gints Plivna)

select id, min_value, max_value, row_number() over (order by min_value, max_value) rank from (
  select distinct id

     , min(value) over (partition by id) min_value
     , max(value) over (partition by id) max_value

  from zzz
)
order by rank ;

solution 2) without a sub-select (Christopher Boyle, Nuno Souto)

SELECT id ,min(value) , max(value) ,
rank() over (order by min(value),max(value)) as ranking FROM zzz
group by id;

Now the only thing left for me to do is figure out which is the most efficient in my "real" query.

-----Message d'origine-----
De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de Nuno Souto Envoyé : jeudi, 30. novembre 2006 17:03
À : oracle-l
Objet : RE: I still have trouble wrapping my head around these analytical functions (should be simple?)

Quoting Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>:

> Unfortunately I really do want the rank.
> As far as your analytics example vs. using min() and max () and group by, I
> will compare to see which is more efficient, but I would imagine that they
> are the same.
>

this does the trick, I believe.
better than the one I sent you before, anyway:

  1 select
  2 id, min (value) as smallest, max (value) as largest,   3 count(1) over (order by min(value),max(value)) rank   4 from zzz
  5 group by id
  6* order by 2,3
next...->/

I SMALLEST LARGEST RANK
- ---------- ---------- ----------

x         10         19          1
b         20         20          2
j         20         30          3
c         30         32          4

4 rows selected.

-- 
Cheers
Nuno Souto
from sunny Sydney
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 30 2006 - 20:11:57 CST