Clauses, Sorting, and SQL Queries - Sorting via Expressions

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