When i first saw Order by 1 at the tail end of an sql script. I was terribly confused.
Select
*
From table1
Order by 1
In this rather crude example above, all the columns from table1 are selected and then ordered by the first column returned by the select.
The number in the where clause is called an “Ordinal”. As it references the columns defined in the select clause
It turns out it is not a good practice to use numbers to represent columns. So i guess my confusion was justified. But here’s the reason why it is not recommended:
- You can’t know at a glance what columns are being explicitly referenced
- Although the query will remain valid even when the column order changes, you risk ordering by some unexpected column.
So yeah this is what it means when you look at some old SQL script and please use it sparingly or better still not at all.