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

Comments On: Clauses, Sorting, and SQL Queries

· This article is an excerpt from the book "Learning SQL," published by O'Reilly. We...   >>> Post your comment now!

Buy this book...

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.