Sql query on rownum

Question

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

After execution it's giving this error:

ORA-00904: "R": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 39 Column: 8

why it's giving this error??

Answer 1

First of all, you can't reference to a second row using a rownum = 2 condition. You can either select first two rows by specifying a where rownum < 3 condition, or you may wrap it in another query and reference your rownum as an ordinary column from over there.

Then, you can't reference a column alias in a where clause of a subquery this alias was assigned. You can either bring it one level up:

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

or just avoid this reference

-- this will return first two rows
SELECT instmax
  FROM (SELECT instmax, rownum r
          FROM (SELECT instmax
                  FROM pswlinstmax
                 ORDER BY instmax DESC NULLS LAST)

         WHERE rownum < 3
         );

Answer 2

Because aliases are not supported in the WHERE clause of the same query. So instead write your query like:

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

Answer 3

A common way to get lets say the top five highly paid employees.

SELECT ename,sal FROM   emp
WHERE  rownum <= 5
ORDER BY sal DESC;

Answer 4

@Amit is right. Because Oracle first evaluates the WHERE condition, then SELECT. You have to do sub-select.

instead of:

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

do the following:

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

...r is now visible to the WHERE clause. Propably this question is identicial/similar/duplicate for this:

Using an Alias in a WHERE clause

Answer 5

You can't reference a column alias in the where clause like that.

The rownum won't quit work that way either. Try something like this:

select instmax from
(
  SELECT instmax, row_number(instmax) order by (instmax desc nulls last) rownumber
  FROM pswlinstmax
)
where rownumber = 2;