confusion on rownum...any help plz?

dilwar · #1 (**permalink**) 12-01-11, 08:37

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

flyboy · #2 (**permalink**) 12-01-11, 09:06

Quote:

Originally Posted by dilwar

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

dilwar · #3 (**permalink**) 12-02-11, 02:02

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

Littlefoot · #4 (**permalink**) 12-02-11, 03:06

Quote:

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:

Quote:

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.

flyboy · #5 (**permalink**) 12-02-11, 03:14

Quote:

Originally Posted by dilwar

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.

aflorin27 · #6 (**permalink**) 12-02-11, 03:14

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

dilwar · #7 (**permalink**) 12-02-11, 04:33

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

flyboy · #8 (**permalink**) 12-02-11, 04:58

Quote:

Originally Posted by dilwar

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

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.

cis_groupie · #9 (**permalink**) 12-06-11, 07:24

Quote:

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?

dilwar · #10 (**permalink**) 12-06-11, 08:20

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

cis_groupie · #11 (**permalink**) 12-09-11, 09:26

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech