How to use rownum [duplicate]

Question

This question already has an answer here:

• How to get second largest or third largest entry from a table 12 answers
• SELECTing top N rows without ROWNUM? 5 answers

I have a employee table in oracle with name,salary and other details.

I am trying to get the second highest salary but not able to fetch.

This one working fine

with e_salary as (select distinct salary from employee)
select salary from e_salary
order by salary desc

And gives output:

450000

61000

60000

50000

40000

30000

20000

6000

but when i am using the same query to fetch second highest row not getting any output

select salary
  from ( with e_salary as (select distinct salary from employee)
         select salary from e_salary order by salary desc)
 where rownum = 2

but as i replace the rownum=2 with rownum<2 it gives output of first two records. Please someone explain why rownum=2 is not working

Answer 1

This will work:

select salary from ( select salary , rownum as rn from (select salary from e_salary order by salary desc)) where rn = 2;

Why it doesn't work:

When assigning ROWNUM to a row, Oracle starts at 1 and only only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.

Hope u are clear right now.

Answer 2

From what I understand, rownum numbers the rows in a result set.

So, in your example:

select * from table1 where rownum=2

How many rows are there going to be in the result set? Therefore, what rownum would be assigned to such a row? Can you see now why no result is actually returned?

In general, you should avoid relying on rownum, or any features that imply an order to results. Try to think about working with the entire set of results.

With that being said, I believe the following would work:

select * from (select rownum as rn,table1.* from table1) as t where t.rn = 2

Because in that case, you're numbering the rows within the subquery.

Answer 3

Without using rownum command you can get the second highest salary by using this query:

select MAX(Salary) from Employee 
WHERE Salary NOT IN 
(select MAX(Salary) from Employee )

or,

select MAX(Salary) from Employee 
WHERE Salary <> 
(select MAX(Salary) from Employee )

query for nth highest:

SELECT * FROM Employee Emp1 
WHERE (N-1) = 
(SELECT COUNT(DISTINCT(Emp2.Salary))FROM Employee Emp2 
WHERE Emp2.Salary > Emp1.Salary)

Answer 4

Use of rownum is a tricky affair. Safest bet is to use it only when you want to limit the number of results to be shown. For example rownum<2 or rownum<=5.

Why rownum=2 will not work?

Read here - http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

In summary, this is how oracle execute a query

1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.

rownum<=2 clause will get converted to

ROWNUM = 1
for x in 
( select * from emp )
loop
    exit when NOT(ROWNUM <= 2)
    OUTPUT record to temp
    ROWNUM = ROWNUM+1
end loop
SORT TEMP

if you change exit when NOT(ROWNUM <= 2) with rownnum=2, you can see it will fail in the first run itself

So if I cannot use rownum, what can I use. Try using row_number() http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

It works something like

SELECT last_name FROM 
   (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
   WHERE R BETWEEN 51 and 100;

Answer 5

rownum in a condition stops evaluating the first time it fails. On the first row returned, rownum is 1, therefore it fails the rownum = 2 test and stops trying. There's an excellent post about it here.

To get the second-highest salary, use the Oracle analytical DENSE_RANK function:

SELECT DISTINCT Salary FROM (
  SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
  FROM e_salary)
WHERE SalaryRank = 2

Note that if there's a tie for second, the query could return more than one value. That's why the outer SELECT is a SELECT DISTINCT.

Answer 6

First you should understand what the rownum is. Let me give you an example,

 you want to get data with a filter and rownum=2, 
 first Oracle executes the sql with filter and get the first record, 
 give it the rownum 1, 
 and then compare it the rownum filter rownum=2, which doesn't match, so discard record, 
 then get second record, give it rownum=1(if the first record is matched then the rownum will  be 2)  too, then do the compare............ 

So you can find the reason.