why both the sql query on rownum are giving different result

Question

>SELECT instmax  
FROM  
  (SELECT instmax,  
     rownum r  
  FROM  
    ( SELECT * FROM pswlinstmax ORDER BY instmax DESC NULLS LAST  
    )   
  )  
WHERE r = 2;  

INSTMAX  
-------  
1049  

>SELECT instmax  
FROM  
  (SELECT instmax,  
    rownum  
  FROM  
    (SELECT * FROM pswlinstmax ORDER BY instmax DESC  
    )  
  )  
WHERE rownum=2;  


**NO RETURNED ROW**  

why it is giving different result? I would like to have detailed explanation on this.

Answer 1

because the secont time, the rownum is not the rownum of the inner SQL but the rownum of the outer one!

that's the reason why you need to "rename" it, so that it becomes "fixed".

otherwise, the rownum =2 filter never matches because every row is the first.

Answer 2

SELECT instmax   
FROM   
  (SELECT instmax,   
    rownum   
  FROM   
    (SELECT * FROM pswlinstmax ORDER BY instmax DESC   
    )   
  )   
WHERE rownum=2;   


**NO RETURNED ROW**   

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned. Oracle9i SQL Reference

In your first example the query in the inline view returns all the rows, each with an incrementing ROWNUM assigned the name r.