RE: I still have trouble wrapping my head around these analytical functions (should be simple?)
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Dec 2006 12:02:53 +1100
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
Other related posts:
