Sorting in User-Defined Orders

6.18.1 Problem

You want to define the sort order for all values in a column.

6.18.2 Solution

Use FIELD( ) to map column values onto a sequence that places the values in the desired order.

6.18.3 Discussion

The previous section showed how to make a specific group of rows go to the head of the sort order. If you want to impose a specific order on all values in a column, use the FIELD( ) function to map them to a list of numeric values and use the numbers for sorting. FIELD( ) compares its first argument to the following arguments and returns a number indicating which one of them it matches. The following FIELD( ) call compares value to str1, str2, str3, and str4, and returns 1, 2, 3, or 4, depending on which one of them value is equal to:

FIELD(value,str1,str2,str3,str4)

The number of comparison values need not be four; FIELD( ) takes a variable-length argument list. If value is NULL or none of the values match, FIELD( ) returns 0.

FIELD( ) can be used to sort an arbitrary set of values into any order you please. For example, to display driver_log records for Henry, Suzi, and Ben, in that order, do this:

mysql> SELECT * FROM driver_log
 -> ORDER BY FIELD(name,'Henry','Suzi','Ben');
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 3 | Henry | 2001-11-29 | 300 |
| 4 | Henry | 2001-11-27 | 96 |
| 6 | Henry | 2001-11-26 | 115 |
| 8 | Henry | 2001-12-01 | 197 |
| 10 | Henry | 2001-11-30 | 203 |
| 2 | Suzi | 2001-11-29 | 391 |
| 7 | Suzi | 2001-12-02 | 502 |
| 1 | Ben | 2001-11-30 | 152 |
| 5 | Ben | 2001-11-29 | 131 |
| 9 | Ben | 2001-12-02 | 79 |
+--------+-------+------------+-------+

You can use FIELD( ) with column substrings, too. To sort items from the housewares table by country of manufacture using the order US, UK, JP, SG, do this:

mysql> SELECT id, description FROM housewares
 -> ORDER BY FIELD(RIGHT(id,2),'US','UK','JP','SG');
+------------+------------------+
| id | description |
+------------+------------------+
| DIN40672US | dining table |
| BTH00485US | shower stall |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven |
| BTH00415JP | lavatory |
| BED00038SG | bedside lamp |
+------------+------------------+

More generally, FIELD( ) can be used to sort any kind of category-based values into specific orders when the categories don't sort naturally into any useful sequence.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net