G-3185
🆓Error
Never use ROWNUM at the same query level as ORDER BY.
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.
Examples
Non-Compliant Example
select first_name
,last_name
,salary
,hire_date
,rownum as salary_rank
from employees
where rownum <= 5
order by salary desc;Issues
| Line | Column | Message |
|---|---|---|
| 7 | 8 | Using ROWNUM at the same query level as ORDER BY. |
Compliant Solution - ★★★★☆
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; Compliant Solution - ★★★★★
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;Explanation
(Assuming you are using Oracle Database 12c or later.)
References
- same as Trivadis G-3185
- same as plsql:S4062