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