Printable View
Hi Kuya,
I read a post here before about a functions thet list top n values,
I know i copied it in my folder because I know its a very useful program, but unfortuntely i can not find the program anymore.
Can u help me again pls....
I just want to list the top 10 employees in table EMP table with the highest SALARY.
Thanks a lot.
Hi,
From oracle 9i u can use the functions RANK(), DENSE_RANK() functions to get the top n rows, so the query will be somethg like this
select x, y, salary from ( select x, y, salary, RANK() OVER (ORDER BY SALARY DESC) SAL from EMP ) where SAL<=10;
in 8i u have to do this with the help of inner query .
Analytic functions (like RANK() and DENSE_RANK()) are available in 8i too.
Yep.
Enterprise Edition only, just in case you are using Standard and wondering why it aint working.
Thank you all my brilliant and lovable kuyas...mwuaahhhh(why is that there's no kiss icon here?):D
Hi kuya Akila,
I tested the sample program in the scott/tiger schema.
I guess the where clause should be rownum<11;
SQL> select empno,ename,sal from emp order by sal desc;
7839 KING 5000
7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7521 WARD 1250
7654 MARTIN 1250
7876 ADAMS 1100
7900 JAMES 950
7369 SMITH 800
14 rows selected.
select empno,ename,sal from (select empno,ename,sal, rank()
over(order by sal desc) sal1 from emp)
where rownum<11 order by sal desc;
7839 KING 5000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7521 WARD 1250
7654 MARTIN 1250
7369 SMITH 800
10 rows selected.
Why is that the output is different?
Thanks again....
no no.. . too much code ;-)Quote:
Originally posted by Akila
Hi,
From oracle 9i u can use the functions RANK(), DENSE_RANK() functions to get the top n rows, so the query will be somethg like this
select x, y, salary from ( select x, y, salary, RANK() OVER (ORDER BY SALARY DESC) SAL from EMP ) where SAL<=10;
in 8i u have to do this with the help of inner query .
SQL> SELECT * FROM
2 (SELECT ename, sal FROM emp ORDER BY sal DESC)
3 WHERE ROWNUM <= 10;
Wow! a simple one....thanks kuya...:cool:
[QUOTE]Originally posted by kris123
[B]Hi kuya Akila,
I tested the sample program in the scott/tiger schema.
I guess the where clause should be rownum<11;
Hai,
if u r willing to use the function Rank() then DONT use the ROWNUM in the WHERE clause , the rows shud be restricted as
select x, y, salary from ( select x, y, salary, RANK() OVER (ORDER BY SALARY DESC) SAL from EMP ) where SAL<=10;
**** note : iam using SAL in WHERE clause.
The other one with ROWNUM is the simple one, (i prefer to use ROWNUM ) i gave u the complicated one bcoz u mentioned abt using functions to do this.
Anyways, happy that u got the results.
Akila Rajakumar
Oh im sorry kuya...i really appreciate your help. Maybe I have to analyze/understand the function more.
By the way SAL<=10 will return no row coz the min(SAL) IS 800.
The max(SAL) is 5000, and if I use ....where SAL<=5000 the output
is also different.
I want to list the top 10 highest paid employee on the EMP table.
Did I miss something here?..
select empno,ename,sal from (select empno,ename,sal, rank()
over(order by sal desc) sal1 from emp)
where rownum<=5000;
Thanks again.:)
Again u r using rownum in the where clause , just forget the rownum,Quote:
Did I miss something here?..
select empno,ename,sal from (select empno,ename,sal, rank()
over(order by sal desc) sal1 from emp)
where rownum<=5000;
now the rank function will order ur salary and the ranks are in
SAL1 ( *** NOT sal) , so in where clause ask for top 10 rankers .
so it shud b like this
select empno,ename,sal from (select empno,ename,sal, rank()
over(order by sal desc) sal1 from emp)
where sal1<=10 ;
here sal1 is not the salary column ie ( SAL in ur query , u r talking abt MAX(sal) and MIN(sal) , iam talking abt sal1 )this is rank, u shud get the top 10 rank holders,
NO MORE ROWNUM , if u want to do it with rownum u don need RANK(),in tat case u can use the query given by Stmontgo in his reply.
hope this works for u .......
Akila Rajakumar
oops just a typo...i actually used sal<=5000...but not the sal1 inner...wait...i run it again
Hi Kuya....
Yes, i got it. it worked now....Thanks a lot again...
I just wonder which code is efficient and runs faster, so i compare the two.
I set time on, so i can see ellapsed time for the 2 programs.
I found out that the simpler code took 5 secs. to process 500,000 rows
while the code with function took 10 secs.
02:07:51 SQL> select rops_amt from
02:07:52 2 (select rops_amt from geninfo where rops_amt is not null order by rops_amt desc)
02:07:52 3 where rownum<11;
ROPS_AMT
----------
315120
252520
250000
193870
181960
177940
169420
164620
161710
159970
10 rows selected.
02:07:57 SQL>
02:08:13 SQL> select rops_amt from (select rops_amt, rank() over(order by rops_amt desc) rops_amt1
02:08:14 2 from geninfo where rops_amt is not null) where rops_amt1<=10;
ROPS_AMT
----------
315120
252520
250000
193870
181960
177940
169420
164620
161710
159970
10 rows selected.
02:08:24 SQL>
:D luv yah all
The problem is that those two queries are two totaly different things, i.e. they answer two totaly different questions! You are comparing apples to oranges.
It is just coincidence that you've got identical results from both of them - but in general they return two different resultsets.
oh i see...thanks for the info kuya...maybe i have to read more about
this rank() function to know when is it appropriate to use. I believe
it not suited for the report i want to obtain.:D