G-3185: Never use ROWNUM at the same query level as ORDER BY.

Major

Reliability, Testability

Reason

The rownum pseudo-column is assigned before the order by clause is used, so using rownum on the same query level as order by will not assign numbers in the desired ordering. Instead you should move the order by into an inline view and use rownum in the outer query.

Example (bad)

select first_name
      ,last_name
      ,salary 
      ,hire_date
      ,rownum as salary_rank
  from employees
 where rownum <= 5
 order by salary desc;

Example (good)

select first_name 
      ,last_name 
      ,salary 
      ,hire_date 
      ,rownum as salary_rank
  from (
   select first_name 
         ,last_name 
         ,salary 
         ,hire_date 
     from employees
    order by salary desc
       )
 where rownum <= 5;