- Sorting Data
- Sorting by Multiple Columns
- Sorting by Column Position
- Specifying Sort Direction
- Summary
This chapter is from the book
This chapter is from the book
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.