Home >Database >Mysql Tutorial >How to Select the Top N Maximum Values in MySQL: Return Only N Rows or Include Ties?
MySQL: Selecting the Top N Maximum Values
Confusion often arises when attempting to retrieve the top N rows with the highest values in a specific column. The question is whether to return only the top N rows or include additional rows if they share the same maximum value.
To achieve the former, the following query will suffice:
SELECT * FROM t ORDER BY value DESC LIMIT N
This query ensures that only the top N rows are returned, regardless of whether other rows have the same maximum value.
However, if the requirement is to include rows with equal maximum values, a more complex query is required:
SELECT * FROM t JOIN ( SELECT MIN(value) AS cutoff FROM ( SELECT value FROM t ORDER BY value LIMIT N ) tlim ) tlim ON t.value >= tlim.cutoff;
Alternatively, the query can be simplified:
SELECT * FROM t JOIN ( SELECT value FROM t ORDER BY value LIMIT N ) tlim ON t.value = tlim.value;
Lastly, another method involves using the ANY function, but its compatibility with MySQL may vary:
SELECT * FROM t WHERE t.value >= ANY ( SELECT value FROM t ORDER BY value LIMIT N )
This approach also includes rows with the same maximum value, offering a conceptually clear solution to the problem.
The above is the detailed content of How to Select the Top N Maximum Values in MySQL: Return Only N Rows or Include Ties?. For more information, please follow other related articles on the PHP Chinese website!