That’s not a problem. You can use columns in theORDER
BY
clause that don’t appear in the output
column list.
ORDER
BY
is not limited to sorting only those
columns named in the output column 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 | +---------------------+---------+-----------+ | 2006-05-12 12:48:13 | tricia | 191K | | 2006-05-14 17:03:01 | tricia | 2339K | | 2006-05-11 10:15:08 | barb | 57K | | 2006-05-14 14:42:21 | barb | 96K | | 2006-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 | +---------------------+---------+-----------+ | 2006-05-11 10:15:08 | barb | 57K | | 2006-05-14 14:42:21 | barb | 96K | | 2006-05-12 12:48:13 | tricia | 191K | | 2006-05-15 10:25:52 | gene | 976K | | 2006-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 that a team has 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 | +---------------------+---------------+---------+ | 2006-05-15 10:25:52 | gene@mars | 998532 | | 2006-05-12 12:48:13 | tricia@mars | 194925 | | 2006-05-11 10:15:08 | barb@saturn | 58274 | | 2006-05-14 17:03:01 | tricia@saturn | 2394482 | | 2006-05-14 14:42:21 | barb@venus | 98151 | +---------------------+---------------+---------+
The same idea commonly is applied to sorting people’s names.
Suppose that you have a table names
that contains last and first names. To display rows 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 | +---------------+
18.117.152.250