Home > Articles

  • Print
  • + Share This
This chapter is from the book

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:


INPUT

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

OUTPUT

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
RYL01    9.4900      King doll
RYL02    9.4900      Queen doll
BR03     11.9900     18 inch teddy bear

ANALYSIS

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, the prod_price column. 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. But there are some downsides too. First, not explicitly listing column names increases the likelihood of you mistakenly specifying the wrong column. Second, it is all too easy to mistakenly reorder data when making changes to the SELECT list (forgetting to make the corresponding changes to the ORDER BY clause). And finally, obviously you cannot use this technique when sorting by columns that are not in the SELECT list.

TIP

Sorting by Nonselected Columns Obviously, this technique cannot be used when sorting by columns that do not appear in the SELECT list. However, you can mix and match actual column names and relative column positions in a single statement if needed.

  • + Share This
  • 🔖 Save To Your Account