AP 18 - Avoid ordinal numbers when using ORDER BY or GROUP BY

Severity

Type

Problem

You can order or group the result set based on the ordinal positions of columns that appear in the SELECT clause

ORDER BY

SELECT col1
      ,col2
      ,col3
  FROM table
 ORDER BY 1
      ,2
      ,3

GROUP BY

SELECT col1
      ,COUNT(9)
  FROM table
 GROUP BY 1

Using the ordinal positions of columns in the ORDER BY / GROUP BY clause is considered an anti pattern

• Your SQL becomes much harder to read for other people
• When you modify the SELECT list, you may forget to make the corresponding changes in the ORDER BY / GROUP BY clause, which will result in errors and bugs.

Solution

Always reference columns by their name and not their ordinal position.

ORDER BY

SELECT col1
      ,col2
      ,col3
  FROM table
 ORDER BY col1
      ,col2
      ,col3

GROUP BY

SELECT col1
      ,COUNT(9)
  FROM table
 GROUP BY col1

Legitimate use of the anti pattern

For ad hoc queries where you need to quickly write up some SQL that you don’t share with other developers and then discard, the anti pattern is legitimate.