Floating Specific Values to the Head or Tail of the Sort Order

6.17.1 Problem

You want a column to sort the way it normally does, except for a few values that you want at a specific spot.

6.17.2 Solution

Add another sort column to the ORDER BY clause that places those few values where you want them. The remaining sort columns will have their usual effect for the other values.

6.17.3 Discussion

If you want to sort a result set normally except that you want particular values first, create an additional sort column that is 0 for those values and 1 for everything else. We used this technique earlier to float NULL values to the high end of the sort order (see Recipe 6.6), but it works for other types of information as well. Suppose you want to sort mail table messages in sender/recipient order, with the exception that you want to put messages for phil first. You can do that like this:

mysql> SELECT t, srcuser, dstuser, size
 -> FROM mail
 -> ORDER BY IF(srcuser='phil',0,1), srcuser, dstuser;
+---------------------+---------+---------+---------+
| t | srcuser | dstuser | size |
+---------------------+---------+---------+---------+
| 2001-05-16 23:04:19 | phil | barb | 10294 |
| 2001-05-12 15:02:49 | phil | phil | 1048 |
| 2001-05-15 08:50:57 | phil | phil | 978 |
| 2001-05-14 11:52:17 | phil | tricia | 5781 |
| 2001-05-17 12:49:23 | phil | tricia | 873 |
| 2001-05-14 14:42:21 | barb | barb | 98151 |
| 2001-05-11 10:15:08 | barb | tricia | 58274 |
| 2001-05-13 13:59:18 | barb | tricia | 271 |
| 2001-05-14 09:31:37 | gene | barb | 2291 |
| 2001-05-16 09:00:28 | gene | barb | 613 |
| 2001-05-15 07:17:48 | gene | gene | 3824 |
| 2001-05-15 17:35:31 | gene | gene | 3856 |
| 2001-05-19 22:21:51 | gene | gene | 23992 |
| 2001-05-15 10:25:52 | gene | tricia | 998532 |
| 2001-05-12 12:48:13 | tricia | gene | 194925 |
| 2001-05-14 17:03:01 | tricia | phil | 2394482 |
+---------------------+---------+---------+---------+

The value of the extra sort column is 0 for rows where the srcuser value is phil, and 1 for all other rows. By making that the most significant sort column, records for messages sent by phil float to the top of the output. (To sink them to the bottom instead, either sort the column in reverse order using DESC, or reverse the order of the second and third arguments of the IF( ) function.)

You can also use this technique for particular conditions, not just specific values. To put first those records where people sent messages to themselves, do this:

mysql> SELECT t, srcuser, dstuser, size
 -> FROM mail
 -> ORDER BY IF(srcuser=dstuser,0,1), srcuser, dstuser;
+---------------------+---------+---------+---------+
| t | srcuser | dstuser | size |
+---------------------+---------+---------+---------+
| 2001-05-14 14:42:21 | barb | barb | 98151 |
| 2001-05-15 07:17:48 | gene | gene | 3824 |
| 2001-05-15 17:35:31 | gene | gene | 3856 |
| 2001-05-19 22:21:51 | gene | gene | 23992 |
| 2001-05-12 15:02:49 | phil | phil | 1048 |
| 2001-05-15 08:50:57 | phil | phil | 978 |
| 2001-05-11 10:15:08 | barb | tricia | 58274 |
| 2001-05-13 13:59:18 | barb | tricia | 271 |
| 2001-05-14 09:31:37 | gene | barb | 2291 |
| 2001-05-16 09:00:28 | gene | barb | 613 |
| 2001-05-15 10:25:52 | gene | tricia | 998532 |
| 2001-05-16 23:04:19 | phil | barb | 10294 |
| 2001-05-14 11:52:17 | phil | tricia | 5781 |
| 2001-05-17 12:49:23 | phil | tricia | 873 |
| 2001-05-12 12:48:13 | tricia | gene | 194925 |
| 2001-05-14 17:03:01 | tricia | phil | 2394482 |
+---------------------+---------+---------+---------+

If you have a pretty good idea about the contents of your table, you can sometimes eliminate the extra sort column. For example, srcuser is never NULL in the mail table, so the previous query can be rewritten as follows to use one less column in the ORDER BY clause (assuming that NULL values sort ahead of all non-NULL values):

mysql> SELECT t, srcuser, dstuser, size
 -> FROM mail
 -> ORDER BY IF(srcuser=dstuser,NULL,srcuser), dstuser;
+---------------------+---------+---------+---------+
| t | srcuser | dstuser | size |
+---------------------+---------+---------+---------+
| 2001-05-14 14:42:21 | barb | barb | 98151 |
| 2001-05-15 07:17:48 | gene | gene | 3824 |
| 2001-05-15 17:35:31 | gene | gene | 3856 |
| 2001-05-19 22:21:51 | gene | gene | 23992 |
| 2001-05-12 15:02:49 | phil | phil | 1048 |
| 2001-05-15 08:50:57 | phil | phil | 978 |
| 2001-05-11 10:15:08 | barb | tricia | 58274 |
| 2001-05-13 13:59:18 | barb | tricia | 271 |
| 2001-05-14 09:31:37 | gene | barb | 2291 |
| 2001-05-16 09:00:28 | gene | barb | 613 |
| 2001-05-15 10:25:52 | gene | tricia | 998532 |
| 2001-05-16 23:04:19 | phil | barb | 10294 |
| 2001-05-14 11:52:17 | phil | tricia | 5781 |
| 2001-05-17 12:49:23 | phil | tricia | 873 |
| 2001-05-12 12:48:13 | tricia | gene | 194925 |
| 2001-05-14 17:03:01 | tricia | phil | 2394482 |
+---------------------+---------+---------+---------+

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