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);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s