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)
| 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.)
| 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
|