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!