Results 1 to 4 of 4

Thread: Query

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Query

    I want to find out 3rd highest salary from emp_details table. This table is having sal column and slaries in thios column are random and unordered.

  2. #2
    Join Date
    Mar 2004
    Location
    Sydney
    Posts
    20
    Code:
    SELECT * FROM 
      (SELECT SALARY, ROWNUM ROWNO FROM 
        (SELECT SALARY FROM EMP_DETAILS ORDER BY SALARY DESC)
      ) U
    WHERE U.ROWNO = 3;
    The inner query "(SELECT SAL FROM EMP_DETAILS ORDER BY SAL DESC)" gives you the list of salaries in the required order.

    The next query "(SELECT SAL, ROWNUM ROWNO FROM
    (...)
    ) U"
    Gives you the salary, the rownum in the correct order and the query alias U to be used in the outer query. The rownum must be aliased to (in this case to rowno) so it can be referenced in the outer query.

    The outer query "SELECT * FROM
    (... (...) ) U
    WHERE U.ROWNO = 3;"
    pulls out the 3rd row, ie the third highest salary.

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Innermost Query

    I am not able to implement ur suggestion. The innermost query is causing a problem.

    U can try it for urself by seprately running innermost query. The error is


    SQL> select * from ( select * from emp_details order by sal desc );
    select * from ( select * from emp_details order by sal desc )
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis


    There is no Sysntax error.Checked it many times.

    Help required.

  4. #4
    Join Date
    Jan 2004
    Location
    Leiden, The Netherlands
    Posts
    11
    Your Oracle version is an important issue .

    TOP-N queries don't work in Oracle 7.3 . You will get the error 'ORA-00907: missing right parenthesis' in Oracle 7.3 .
    Last edited by rcoerwin; 03-17-04 at 13:48.

Posting Permissions

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