How to Simulate Rank in MySQL to Select Top n Maximum Values?

Simulating Rank with MySQL to Select Top n Maximum Values

Selecting the top n maximum values from a table can be challenging in MySQL, especially when the desired n is greater than the number of distinct groups in the table.

Approximating Top n Max Values Using a Union Query

For n=2, we can approximate the desired output using a union query:

SELECT max(column1) m
FROM table t
GROUP BY column2
UNION
SELECT max(column1) m
FROM table t
WHERE column1 NOT IN (SELECT max(column1) WHERE column2 = t.column2)

This query first finds the maximum value for each group and then finds the second maximum value of all values that are not already the maximum value of any group.

Utilizing Rank Simulations

For any n, we can simulate rank over partition using techniques described elsewhere. One such approach is to use a subquery that returns the n-th maximum value for each group:

SELECT t.*
FROM
   (SELECT grouper,
          (SELECT val
           FROM table li
           WHERE li.grouper = dlo.grouper
           ORDER BY
                 li.grouper, li.val DESC
           LIMIT 2,1) AS mid
   FROM
      (
      SELECT DISTINCT grouper
      FROM table
      ) dlo
   ) lo, table t
WHERE t.grouper = lo.grouper
      AND t.val > lo.mid

In this query, LIMIT 2,1 sets the n value to 2. Replace grouper with the grouping column name and val with the value column name in your specific scenario.

The above is the detailed content of How to Simulate Rank in MySQL to Select Top n Maximum Values?. For more information, please follow other related articles on the PHP Chinese website!