Database Reference
In-Depth Information
The technique of displaying one value but sorting by another is also useful when you
display values composed from multiple columns that don't sort the way you want. For
example, the
mail
table lists message senders using separate
srcuser
and
srchost
values. To display message senders from the
mail
table as email addresses in
srcus
er@srchost
format with the username first, 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 |
+---------------------+---------------+---------+
| 2014-05-15 10:25:52 | gene@mars | 998532 |
| 2014-05-12 12:48:13 | tricia@mars | 194925 |
| 2014-05-11 10:15:08 | barb@saturn | 58274 |
| 2014-05-14 17:03:01 | tricia@saturn | 2394482 |
| 2014-05-14 14:42:21 | barb@venus | 98151 |
+---------------------+---------------+---------+
The same idea commonly applies to sorting people's names. Suppose that a
names
table
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, 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 last-name order? Display
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
