How to seach a prevoius post

[kris123]

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.

[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 .

[jmodic]

Analytic functions (like RANK() and DENSE_RANK()) are available in 8i too.

[waitecj]

Yep.

Enterprise Edition only, just in case you are using Standard and wondering why it aint working.

[kris123]

Thank you all my brilliant and lovable kuyas...mwuaahhhh(why is that there's no kiss icon here?)

[kris123]

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....

[stmontgo]

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 .

no no.. . too much code ;-)


SQL> SELECT * FROM
2 (SELECT ename, sal FROM emp ORDER BY sal DESC)
3 WHERE ROWNUM <= 10;

[kris123]

Wow! a simple one....thanks kuya...

[Akila]

[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

[kris123]

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.