Skip to content

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

Blocker

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)

1 2 3 4 5 6 7 8
select first_name  ,last_name  ,salary  ,hire_date  ,rownum as salary_rank  from employees  where rownum <= 5 -- violates also G-1050 literal is ok for a standalone query  order by salary desc;

Example (good)

 1  2  3  4  5  6  7  8  9 10 11 12 13 14
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; -- NOSONAR: G-1050 literal is ok for a standalone query

Example (best)

(Assuming you are using Oracle Database 12c or later.)

1 2 3 4 5 6 7 8
select first_name  ,last_name  ,salary  ,hire_date  ,rank() over (order by salary desc) as salary_rank  from employees  order by salary desc fetch first 5 rows only; -- NOSONAR: G-1050 literal is ok for a standalone query