- 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 Multiple Columns
It is often necessary to sort data by more than one column. For example, if you are displaying an employee list, you might want to display it sorted by last name and first name (first by last name, and then within each last name sort by first name). This would be useful if there are multiple employees with the same last name.
To sort by multiple columns, simply specify the column names separated by commas (just as you do when you are selecting multiple columns).
The following code retrieves three columns and sorts the results by two of them—first by price and then by name.
INPUT
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
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
It is important to understand that when you are sorting by multiple columns, the sort sequence is exactly as specified. In other words, using the output in the example above, the products are sorted by the prod_name column only when multiple rows have the same prod_price value. If all the values in the prod_price column had been unique, no data would have been sorted by prod_name.