Did You Know #18 – Order By Position

When writing a query with order by, we can use the column position instead of its name. This order by”trick” is easy to use, but should be handled carefully as it affects the order of rows if the column list changes.

For example, these two are equal:
select col1,col2,col3 from table1 order by col1,col2;
select col1,col2,col3 from table1 order by 1,2;
But if we add a column to the beginning of the query, the order will be completely different:
select new_col,col1,col2,col3 from table1 order by col1,col2; => same order as before
select new_col,col1,col2,col3 from table1 order by 1,2; => the order will be new_col,col1
Another interesting thing that I just realized, is that you can only use the column position as is, without any manipulations. I tried to order product names in a case insensitive way, but this doesn’t work:
select product_name from products order by lower(1);
The result was simply not ordered. The only way to order by a manipulation on a column is to provide its name (or alias):
select product_name from products order by lower(product_name);