| RANK function [message #45266] |
Fri, 27 February 2004 10:02  |
Nicola
Messages: 14
Registered: March 2002
|
Junior Member |
|
|
We are trying to use the rank function in SQL, but from all the examples we've seen they have code such as 'where deptno = 10' and we need to set as rank <= 5
Is this possible as we keep getting errors?
|
|
|
|
| Re: RANK function [message #45268 is a reply to message #45266] |
Fri, 27 February 2004 11:22  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I will guess that the error you are getting is ORA-30483: window functions are not allowed here. In the future, it is always helpful if you provide the actual statement you are attempting to execute and the actual error message.
If you are getting the ORA-30483, just use an inline view and then apply the filter to the results of that view:
sql>select empno, sal, rank() over (order by sal) rnk
2 from emp;
EMPNO SAL RNK
--------- --------- ---------
7369 800 1
7900 950 2
7876 1100 3
7521 1250 4
7654 1250 4
7934 1300 6
7844 1500 7
7499 1600 8
7782 2450 9
7698 2850 10
7566 2975 11
7788 3000 12
7902 3000 12
7839 5000 14
14 rows selected.
sql>select empno, sal, rank() over (order by sal) rnk
2 from emp
3 where rank() over (order by sal) <= 5;
where rank() over (order by sal) <= 5
*
ERROR at line 3:
ORA-30483: window functions are not allowed here
sql>select *
2 from (select empno, sal, rank() over (order by sal) rnk
3 from emp)
4 where rnk <= 5;
EMPNO SAL RNK
--------- --------- ---------
7369 800 1
7900 950 2
7876 1100 3
7521 1250 4
7654 1250 4
5 rows selected. |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
