Clauses, Sorting, and SQL Queries - Sorting via Expressions
(Page 4 of 5 )
Sorting your results using column data is all well and good, but sometimes you might need to sort by something that is not stored in the database, and possibly doesn’t appear anywhere in your query. You can add an expression to your order by clause to handle such situations. For example, perhaps you would like to sort your customer data by the last three digits of the customer’s Federal ID number (which is either a Social Security number for individuals or a corporate ID for businesses):
mysql> SELECT cust_id, cust_type_cd, city, state, fed_id
-> FROM customer
-> ORDER BY RIGHT(fed_id, 3);
|
cust_id cust_type_cd city | state fed_id | |
|
| 1 I | Lynnfield | MA | 111-11-1111 |
| 10 B | Salem | NH | 04-1111111 | |
| 2 I | Woburn | MA | 222-22-2222 |
| 11 B | Wilmington MA | 04-2222222 | |
| 3 I | Quincy | MA | 333-33-3333 |
| 12 | B | Salem | NH | 04-3333333 | |
| 13 B | Quincy | MA | 04-4444444 | |
| 4 I | Waltham | MA | 444-44-4444 |
| 5 I | Salem | NH | 555-55-5555 |
| 6 I | Waltham | MA | 666-66-6666 |
| 7 I | Wilmington MA | 777-77-7777 |
| 8 I | Salem | NH | 888-88-8888 |
| 9 I | Newton | MA | 999-99-9999 |
|
13 rows in set (0.01 sec)
This query uses the built-in functionright()to extract the last three characters of thefed_idcolumn and then sorts the rows based on this value.
Next: Sorting via Numeric Placeholders >>
More Database Articles Articles
More By O'Reilly Media
|
This article is excerpted from chapter three of the book Learning SQL, written by Alan Beaulieu (O'Reilly; ISBN: 0596007272). Check it out today at your favorite bookstore. Buy this book now.
|
|