How to seach a prevoius post
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: How to seach a prevoius post

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    How to seach a prevoius post

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    Chennai
    Posts
    38
    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 .

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Analytic functions (like RANK() and DENSE_RANK()) are available in 8i too.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Yep.

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

  5. #5
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Thank you all my brilliant and lovable kuyas...mwuaahhhh(why is that there's no kiss icon here?)

  6. #6
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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....

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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;
    I'm stmontgo and I approve of this message

  8. #8
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Wow! a simple one....thanks kuya...

  9. #9
    Join Date
    Nov 2004
    Location
    Chennai
    Posts
    38
    [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

  10. #10
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width