Choosing Appropriate LIMIT Values
Problem
LIMIT
doesn’t seem to do what you want it to.
Solution
Be sure that you understand what question you’re asking. It may
be that LIMIT
is exposing some
interesting subtleties in your data that you have not
considered.
Discussion
LIMIT
n
is useful in conjunction with ORDER
BY
for selecting smallest or largest values
from a result set. But does that actually give you the rows with the
n
smallest or largest values? Not
necessarily! It does if your rows contain unique values, but not if
there are duplicates. You may find it necessary to run a preliminary
query first to help you choose the proper LIMIT
value.
To see why this is, consider the following dataset, which shows
the American League pitchers who won 15 or more games during the 2001
baseball season (you can find this data in the al_winner.sql file in the tables directory of the recipes
distribution):
mysql>SELECT name, wins FROM al_winner
->ORDER BY wins DESC, name;
+----------------+------+ | name | wins | +----------------+------+ | Mulder, Mark | 21 | | Clemens, Roger | 20 | | Moyer, Jamie | 20 | | Garcia, Freddy | 18 | | Hudson, Tim | 18 | | Abbott, Paul | 17 | | Mays, Joe | 17 | | Mussina, Mike | 17 | | Sabathia, C.C. | 17 | | Zito, Barry | 17 | | Buehrle, Mark | 16 | | Milton, Eric | 15 | | Pettitte, Andy | 15 | | Radke, Brad | 15 | | Sele, Aaron | 15 | +----------------+------+
If you want to know who won the most games, adding LIMIT
1
to the preceding statement gives you the correct answer ...
Get MySQL Cookbook, 2nd Edition 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.