MySQL Cookbook

6.5 Displaying One Set of Values While Sorting by Another

6.5.1 Problem

You want to sort a result set using values that you're not selecting.

6.5.2 Solution

That's not a problem. You can use columns in the ORDER BY clause that don't appear in the column output list.

6.5.3 Discussion

ORDER BY is not limited to sorting only those columns named in the column output list. It can sort using values that are "hidden" (that is, not displayed in the query output). This technique is commonly used when you have values that can be represented different ways and you want to display one type of value but sort by another. For example, you may want to display mail message sizes not in terms of bytes, but as strings such as 103K for 103 kilobytes. You can convert a byte count to that kind of value using this expression:

CONCAT(FLOOR((size+1023)/1024),'K')

However, such values are strings, so they sort lexically, not numerically. If you use them for sorting, a value such as 96K sorts after 2339K, even though it represents a smaller number:

mysql> SELECT t, srcuser,
    -> CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
    -> FROM mail WHERE size > 50000
    -> ORDER BY size_in_K;
+---------------------+---------+-----------+
| t                   | srcuser | size_in_K |
+---------------------+---------+-----------+
| 2001-05-12 12:48:13 | tricia  | 191K      |
| 2001-05-14 17:03:01 | tricia  | 2339K     |
| 2001-05-11 10:15:08 | barb    | 57K       |
| 2001-05-14 14:42:21 | barb    | 96K       |
| 2001-05-15 10:25:52 | gene    | 976K      |
+---------------------+---------+-----------+

To achieve the desired output order, display the string, but use the actual numeric size for sorting:

mysql> SELECT t, srcuser,
    -> CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
    -> FROM mail WHERE size > 50000
    -> ORDER BY size;
+---------------------+---------+-----------+
| t                   | srcuser | size_in_K |
+---------------------+---------+-----------+
| 2001-05-11 10:15:08 | barb    | 57K       |
| 2001-05-14 14:42:21 | barb    | 96K       |
| 2001-05-12 12:48:13 | tricia  | 191K      |
| 2001-05-15 10:25:52 | gene    | 976K      |
| 2001-05-14 17:03:01 | tricia  | 2339K     |
+---------------------+---------+-----------+

Displaying values as strings but sorting them as numbers also can bail you out of some otherwise difficult situations. Members of sports teams typically are assigned a jersey number, which normally you might think should be stored using a numeric column. Not so fast! Some players like to have a jersey number of zero (0), and some like double-zero (00). If a team happens to have players with both numbers, you cannot represent them using a numeric column, because both values will be treated as the same number. The way out of the problem is to store jersey numbers as strings:

CREATE TABLE roster
(
    name        CHAR(30),       # player name
    jersey_num  CHAR(3)         # jersey number
);

Then the jersey numbers will display the same way you enter them, and 0 and 00 will be treated as distinct values. Unfortunately, although representing numbers as strings solves the problem of distinguishing 0 and 00, it introduces a different problem. Suppose a team comprises the following players:

mysql> SELECT name, jersey_num FROM roster;
+-----------+------------+
| name      | jersey_num |
+-----------+------------+
| Lynne     | 29         |
| Ella      | 0          |
| Elizabeth | 100        |
| Nancy     | 00         |
| Jean      | 8          |
| Sherry    | 47         |
+-----------+------------+

The problem occurs when you try to sort the team members by jersey number. If those numbers are stored as strings, they'll sort lexically, and lexical order often differs from numeric order. That's certainly true for the team in question:

mysql> SELECT name, jersey_num FROM roster ORDER BY jersey_num;
+-----------+------------+
| name      | jersey_num |
+-----------+------------+
| Ella      | 0          |
| Nancy     | 00         |
| Elizabeth | 100        |
| Lynne     | 29         |
| Sherry    | 47         |
| Jean      | 8          |
+-----------+------------+

The values 100 and 8 are out of place. But that's easily solved. Display the string values, but use the numeric values for sorting. To accomplish this, add zero to the jersey_num values to force a string-to-number conversion:

mysql> SELECT name, jersey_num FROM roster ORDER BY jersey_num+0;
+-----------+------------+
| name      | jersey_num |
+-----------+------------+
| Ella      | 0          |
| Nancy     | 00         |
| Jean      | 8          |
| Lynne     | 29         |
| Sherry    | 47         |
| Elizabeth | 100        |
+-----------+------------+

The technique of displaying one value but sorting by another is also useful when you want to display composite values that are formed from multiple columns but that don't sort the way you want. For example, the mail table lists message senders using separate srcuser and srchost values. If you want to display message senders from the mail table as email addresses in srcuser@srchost format with the username first, you can construct those values using the following expression:

CONCAT(srcuser,'@',srchost)

However, those values are no good for sorting if you want to treat the hostname as more significant than the username. Instead, sort the results using the underlying column values rather than the displayed composite values:

mysql> SELECT t, CONCAT(srcuser,'@',srchost) AS sender, size
    -> FROM mail WHERE size > 50000
    -> ORDER BY srchost, srcuser;
+---------------------+---------------+---------+
| t                   | sender        | size    |
+---------------------+---------------+---------+
| 2001-05-15 10:25:52 | gene@mars     |  998532 |
| 2001-05-12 12:48:13 | tricia@mars   |  194925 |
| 2001-05-11 10:15:08 | barb@saturn   |   58274 |
| 2001-05-14 17:03:01 | tricia@saturn | 2394482 |
| 2001-05-14 14:42:21 | barb@venus    |   98151 |
+---------------------+---------------+---------+

The same idea commonly is applied to sorting people's names. Suppose you have a table names that contains last and first names. To display records sorted by last name first, the query is straightforward when the columns are displayed separately:

mysql> SELECT last_name, first_name FROM name
    -> ORDER BY last_name, first_name;
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| Blue      | Vida       |
| Brown     | Kevin      |
| Gray      | Pete       |
| White     | Devon      |
| White     | Rondell    |
+-----------+------------+

If instead you want to display each name as a single string composed of the first name, a space, and the last name, you can begin the query like this:

SELECT CONCAT(first_name,' ',last_name) AS full_name FROM name ...

But then how do you sort the names so they come out in the last name order? The answer is to display the composite names, but refer to the constituent values in the ORDER BY clause:

mysql> SELECT CONCAT(first_name,' ',last_name) AS full_name
    -> FROM name
    -> ORDER BY last_name, first_name;
+---------------+
| full_name     |
+---------------+
| Vida Blue     |
| Kevin Brown   |
| Pete Gray     |
| Devon White   |
| Rondell White |
+---------------+

If you want to write queries that sort on non-displayed values, you'll have problems if the sort columns are expressions and you're using an older version of MySQL. This is because expressions aren't allowed in ORDER BY clauses until MySQL 3.23.2 (as discussed in Recipe 6.4).

The solution is to "unhide" the expression—add it as an extra output column, and then refer to it by position or by using an alias. For example, to write a query that lists names from the names table with the longest names first, you might do this in MySQL 3.23.2 and up:

mysql> SELECT CONCAT(first_name,' ',last_name) AS name
    -> FROM names
    -> ORDER BY LENGTH(CONCAT(first_name,' ',last_name)) DESC;
+---------------+
| name          |
+---------------+
| Rondell White |
| Kevin Brown   |
| Devon White   |
| Vida Blue     |
| Pete Gray     |
+---------------+

To rewrite this query for older versions of MySQL, put the expression in the output column list and use an alias to sort it:

mysql> SELECT CONCAT(first_name,' ',last_name) AS name,
    -> LENGTH(CONCAT(first_name,' ',last_name)) AS len
    -> FROM names
    -> ORDER BY len DESC;
+---------------+------+
| name          | len  |
+---------------+------+
| Rondell White |   13 |
| Kevin Brown   |   11 |
| Devon White   |   11 |
| Vida Blue     |    9 |
| Pete Gray     |    9 |
+---------------+------+

Or else refer to the additional output column by position:

mysql> SELECT CONCAT(first_name,' ',last_name) AS name,
    -> LENGTH(CONCAT(first_name,' ',last_name)) AS len
    -> FROM names
    -> ORDER BY 2 DESC;
+---------------+------+
| name          | len  |
+---------------+------+
| Rondell White |   13 |
| Kevin Brown   |   11 |
| Devon White   |   11 |
| Vida Blue     |    9 |
| Pete Gray     |    9 |
+---------------+------+

Whichever workaround you use, the output will of course contain a column that's there only for sorting purposes and that you really aren't interested in displaying. If you're running the query from the mysql program, that's unfortunate, but there's nothing you can do about the additional output. In your own programs, the extra output column is no problem. It'll be returned in the result set, but you can ignore it. Here's a Python example that demonstrates this. It runs the query, displays the names, and discards the name lengths:

cursor = conn.cursor (MySQLdb.cursors.DictCursor)
cursor.execute ("""
        SELECT CONCAT(first_name,' ',last_name) AS full_name,
        LENGTH(CONCAT(first_name,' ',last_name)) AS len
        FROM name
        ORDER BY len DESC
                """)
for row in cursor.fetchall ( ):
    print row["full_name"]  # print name, ignore length
cursor.close ( )