What to Do When LIMIT Requires the “Wrong” Sort Order
Problem
LIMIT
usually works best in conjunction with an ORDER
BY
clause that sorts rows. But sometimes the sort
order is the opposite of what you want for the final result.
Solution
Rewrite the query, or write a program that retrieves the rows and sorts them into the order you want.
Discussion
If you want the last four records of a result set, you can obtain
them easily by sorting the set in reverse order and using
LIMIT
4
. For example, the
following query returns the names and birth dates for the four people
in the profile
table who were born most recently:
mysql> SELECT name, birth FROM profile ORDER BY birth DESC LIMIT 4;
+---------+------------+
| name | birth |
+---------+------------+
| Shepard | 1975-09-02 |
| Carl | 1973-11-02 |
| Fred | 1970-04-13 |
| Mort | 1969-09-30 |
+---------+------------+
But that requires sorting the birth
values in
descending order to place them at the head of the result set. What if
you want them in ascending order instead? One way to solve this
problem is to use two queries. First, use COUNT( )
to find out how many rows are in the table:
mysql> SELECT COUNT(*) FROM profile;
+----------+
| COUNT(*) |
+----------+
| 10 |
+----------+
Then, sort the values in ascending order and use the two-argument
form of LIMIT
to skip all but the last four
records:
mysql> SELECT name, birth FROM profile ORDER BY birth LIMIT 6, 4;
+---------+------------+ | name | birth | +---------+------------+ | Mort | 1969-09-30 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.