Sorting by Column Position

In addition to being able to specify sort order using column names, ORDER BY also supports ordering specified by relative column position. The best way to understand this is to look at an example:

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

prod_id     prod_price     prod_name
-------     ----------     --------------------
BNBG02      3.4900         Bird bean bag toy
BNBG01      3.4900         Fish bean bag toy
BNBG03      3.4900         Rabbit bean bag toy
RGAN01      4.9900         Raggedy Ann
BR01        5.9900         8 inch teddy bear
BR02        8.9900         12 inch teddy bear
BR03        11.9900        18 inch teddy bear

As you can see, the output is identical to that of the query above. The difference here is in the ORDER BY clause. Instead of specifying column names, the relative positions of selected columns in the SELECT list are specified. ORDER BY 2 means sort by the second column in the SELECT list. ORDER BY 2, 3 means sort by prod_price and then by prod_name.

The primary advantage of this technique is that it saves retyping the column names. The downside is that it is all too easy to mistakenly reorder data when making changes to the SELECT list.