Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2011
    Posts
    4

    Question Unanswered: confusion on rownum...any help plz?

    select min(sal) from (select sal from employee where rownum<5 order by file_id desc);

    sub_query correctly returns the max 4 salaries from the table. But total query is giving some unexpected output.Its not showing the min salary from those 4 results returned by the sub query.

    WHAT is actually happening here? I dont need the right query to get the 4th highest salary but want to get cleared about what the wrong is with my query..


    THNKS in ADVANCE....

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by dilwar View Post
    select min(sal) from (select sal from employee where rownum<5 order by file_id desc);

    sub_query correctly returns the max 4 salaries from the table.
    How did you come to this conclusion? Because, it is wrong. Just run the subquery itself, the subquery without WHERE clause and analyze the results.

    As ROWNUM filter is applied before ORDER BY clause, 4 random rows are fetched from EMPLOYEES table and then they are sorted. Random means, that they may be different each time you run the query - the result is not deterministic. By the way, you are ordering by FILE_ID, so I would not call it "max salaries", but top 4 FILE_IDs.

    You may find the same explanation (following correct ways how to do TOP N query) in this article:
    http://www.oracle-base.com/articles/...opNQueries.php

  3. #3
    Join Date
    Dec 2011
    Posts
    4

    Wink

    the sub query is fetching the top 4 results correctly. (Sorry, it will be ORDER BY SAL,not FILE_ID.....i mistyped it)

    i fired the sub query again to make sure that it fetches the expected top four results.....

    but, with the whole query, some unexpected result is being shown...not the minimum among the 4 results fetched by the subquery...plz clarify if u can.....

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    the sub query is fetching the top 4 results correctly
    No, it is not.

    All salaries:
    Code:
    SQL> select ename, sal from emp order by sal desc;
    
    ENAME             SAL
    ---------- ----------
    KING             5800
    FORD             3800
    SCOTT            3800
    JONES            3775
    BLAKE            3650
    CLARK            3250
    ALLEN            2400
    TURNER           2300
    MILLER           2100
    WARD             2050
    MARTIN           2050
    ADAMS            1900
    JAMES            1750
    SMITH            1600
    Your subquery:
    Code:
    SQL> select sal from emp where rownum < 5 order by sal desc;
    
           SAL
    ----------
          3775
          2400
          2050
          1600
    These are, obviously, not top 4 salaries from the EMP table.

    Therefore, read what Flyboy said.

    Also, it would help if you could demonstrate what you have and what you'd want to get as a result. You said:
    I dont need the right query to get the 4th highest salary but want to get cleared about what the wrong is with my query.
    There's nothing wrong with it - it does what you told it to do, but I still don't understand what result you expected. So, please, run SQL*Plus and copy/paste what you did, what you got, and what you thought you should have got.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by dilwar View Post
    the sub query is fetching the top 4 results correctly. (Sorry, it will be ORDER BY SAL,not FILE_ID.....i mistyped it)

    i fired the sub query again to make sure that it fetches the expected top four results.....

    but, with the whole query, some unexpected result is being shown...not the minimum among the 4 results fetched by the subquery...plz clarify if u can.....
    What do you not understand in "they may be different each time you run the query"?
    In the subquery alone, you fetch four random rows.
    In the whole query, you fetch four random rows. They may be same as the previous ones, but they may be different as well. In your case, the second case happened.
    As the query is not deterministic, both (and all possible other rows returned) are correct. If you want the query to return the same rows, you have to determine exact conditions for obtaining exactly those four rows. What are you really trying to achieve.

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You should use subqueries, like this:

    Code:
    SELECT MIN(B.sal)
    FROM
    (
    SELECT A.sal
    FROM
    (
    SELECT sal
    FROM EMPLOYEE
    ORDER BY sal DESC
    ) A
    WHERE ROWNUM < 5
    ) B
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Dec 2011
    Posts
    4
    okay..i understand what u both told.
    But I am getting the top 4 results by the sub query, not random and same result I got atleast 10 times I fired the query.
    I am using oracle SQL Developer. Does it depend on what I am firing the query???
    I want to get the 4th highest sal. And trust me, reading ur posts, I fired the query atleast 10 times just to get the top 4 results correctly...
    I guess it may depend what I am using like SQL*PLUS or SQL Developer or so...

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by dilwar View Post
    okay..i understand what u both told.
    But I am getting the top 4 results by the sub query, not random and same result I got atleast 10 times I fired the query.
    I am using oracle SQL Developer. Does it depend on what I am firing the query???
    Even top 4 results are random ones. It is internal decision of Oracle, which rows will it fetch. It might be lazy to access data every time you issue the query and use results of the previous run of the same query from the cache instead. You may trace the session to see what is happening there. The description of its steps is described e.g. in this article: http://www.oracle-base.com/articles/...dTkprof10g.php
    Quote Originally Posted by dilwar View Post
    I want to get the 4th highest sal. And trust me, reading ur posts, I fired the query atleast 10 times just to get the top 4 results correctly...
    But, in your initial post, you stated: "I dont need the right query to get the 4th highest salary", so I am puzzled, as your query is definitely not the TOP N one (as you was told here multiple times). Either use the one posted by aflorin27 or pick any other from the link I posted. The exact query depends on your definition of "4th highest sal" in case, when two (or more) employees have the same salary - but it is also described in the article from the link I posted.

  9. #9
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    okay..i understand what u both told
    I don't think you do understand. What happens with your query when you change it to "where rownum > 5"? According to you, you would expect all salaries except for the top 5 to be returned. But what actually happens? And, more importantly, why?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  10. #10
    Join Date
    Dec 2011
    Posts
    4
    Dear cis-groupe

    I will not expect any row to be returned when the condition is changed to rownum > 6 .......

    for the very first time, the condition will be evaluated False..

  11. #11
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Correct. Because Oracle returns any rows, and then assigns a rownum to each returned row.

    So your query "select sal from employee where rownum<5" is returning any 4 rows from the table & assigning rownums to them. It's pure luck as to which rows are returned.

    A determining factor in the randomness (or perceived lack of randomness) of the selection is whether or not there is an index in use.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Tags for this Thread

Posting Permissions

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