Selecting Data from Tables - MySQL - page 125

mysql> SELECT name, birth FROM profile ORDER BY birth DESC LIMIT 4;
+-------+------------+
| name | birth |
+-------+------------+
| Ralph | 1973-11-02 |
| Sybil | 1970-04-13 |
| Nancy | 1969-09-30 |
| Aaron | 1968-09-17 |
+-------+------------+
But that requires sorting the birth values in descending order to place them at the head
of the result set. What if you want the output rows to appear in ascending order instead?
Use the SELECT as a subquery of an outer statement that re-sorts the rows in the desired
final order:
mysql> SELECT * FROM
-> (SELECT name, birth FROM profile ORDER BY birth DESC LIMIT 4) AS t
-> ORDER BY birth;
+-------+------------+
| name | birth |
+-------+------------+
| Aaron | 1968-09-17 |
| Nancy | 1969-09-30 |
| Sybil | 1970-04-13 |
| Ralph | 1973-11-02 |
+-------+------------+
AS t is used here because any table referred to in the FROM clause must have a name, even
a “derived” table produced from a subquery.
3.11. Calculating LIMIT Values from Expressions
Problem
You want to use expressions to specify the arguments for LIMIT .
Solution
Sadly, you cannot. LIMIT arguments must be literal integers—unless you issue the state‐
ment in a context that permits the statement string to be constructed dynamically. In
that case, you can evaluate the expressions yourself and insert the resulting values into
the statement string.
Discussion
Arguments to LIMIT must be literal integers, not expressions. Statements such as the
following are illegal: