Clauses, Sorting, and SQL Queries - Sorting via Numeric Placeholders

(Page 5 of 5 )

If you are sorting using the columns in your select clause, you can opt to reference the columns by their position in the select clause rather than by name. For example, if you want to sort using the second and fifth columns returned by a query, you could do the following:

    mysql> SELECT emp_id, title, start_date, fname, lname
        -> FROM employee
        -> ORDER BY 2, 5;

 

emp_id title		start_date fname					lname
	13  Head Teller		2000-05-11  John						Blake
	6 Head Teller		2004-03-17  Helen						Fleming
	16  Head Teller		2001-03-15  Theresa						Markham
	10  Head Teller		2002-07-27 Paula						Roberts
	5  Loan Manager			2003-11-14  John					Gooding
	4 Operations Manager 2002-04-24 | Susan								Hawthorne
	1  President				2001-06-22  Michael  Smith
	17 Teller				2002-06-29  Beth	Fowler
	9  Teller				2002-05-03  Jane	Grossman
	12  Teller				2003-01-08  Samantha  Jameson
	14  Teller				2002-08-09 Cindy			Mason
	8  Teller				2002-12-02  Sarah			Parker
	15  Teller				2003-04-01  Frank			Portman
	7  Teller				2004-09-15  Chris			Tucker
	18 Teller				2002-12-12  Rick			Tulman
	11  Teller				2000-10-23  Thomas			Ziegler
	3Treasurer				2000-02-09  Robert			Tyler
	2  Vice President				2002-09-12  Susan			Barker

18 rows in set (0.03 sec)

You might want to use this feature sparingly, since adding a column to theselectclause without changing the numbers in theorder byclause can lead to unexpected results.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

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.