Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
SQL: ORDER BY [column index]
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SQL-92 standard and most implementations have always supported ordering by column index (though the feature has been removed already from SQL-99!). It can be useful for quick and dirty queries like these:
SELECT ID, FIRST_NAME || ' ' || LAST_NAME FROM AUTHOR ORDER BY 2;
The above is equivalent to this:
SELECT ID, FIRST_NAME || ' ' || LAST_NAME FROM AUTHOR ORDER BY FIRST_NAME || ' ' || LAST_NAME;
But it is easy to see that this query may quickly break by introducing another column in the projection, anywhere before the sorted column with index 2
-- Adding a column has broken the query SELECT ID, LAST_UPDATE, FIRST_NAME || ' ' || LAST_NAME FROM AUTHOR ORDER BY 2;
Alternatively, these variants would not have broken:
-- The query is not affected by this change SELECT ID, LAST_UPDATE, FIRST_NAME || ' ' || LAST_NAME FROM AUTHOR ORDER BY FIRST_NAME || ' ' || LAST_NAME; -- Also, aliasing the expression (to avoid repetition) can work in many dialects SELECT ID, LAST_UPDATE, FIRST_NAME || ' ' || LAST_NAME AS NAME FROM AUTHOR ORDER BY NAME;
While for quick ad-hoc queries, the ORDER BY [column index] feature can be occasionally useful, it's generally good to simply avoid the feature, especially when using jOOQ, which makes reusing query parts very simple.
Feedback
Do you have any feedback about this page? We'd love to hear it!