G-3180

🆓

Warning

Always specify column names instead of positional references in ORDER BY clauses.

Rule Details

Severity Level: Major
Quality Area: Reliability
Fixing times: 1 mins
Local-easy
Database Support: OracleDB (v7.3.4 +)

Reason

If you change your select list afterwards the order by will still work but order your rows differently, when not changing the positional number. Furthermore, it is not comfortable to the readers of the code, if they have to count the columns in the select list to know the way the result is ordered.

Example

Non-Compliant Example

select upper(first_name)
      ,last_name
      ,salary
      ,hire_date
  from employees
 order by 4,1,3;

Issues

Line	Column	Message
6	11	Positional reference in ORDER BY clause.
6	13	Positional reference in ORDER BY clause.
6	15	Positional reference in ORDER BY clause.

Compliant Solution - ★★★★★

select upper(first_name) as first_name
      ,last_name
      ,salary
      ,hire_date
  from employees
 order by hire_date
      ,first_name
      ,salary;

References

similar to SQLFluff ambiguous.column_references
The scope of ambiguous.column_references is ORDER BY and GROUP BY, forcing either position numbers or column names.
same as plsql:OrderByColumnNumberCheck
same as Trivadis G-3180