RE: I still have trouble wrapping my head around these analytical functions (should be simple?)
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Nov 2006 18:11:57 -0800
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx>:
> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
