Lin Ma Lin Ma - 13 days ago 9
MySQL Question

find the N-th largest element in SQL

Wondering how to write a SQL function to find the N-th largest element in a table, and if no N-th largest element, return Null.

Using MySQL/MySQL workbench.

BTW, my question is different from the N-th highest salary question since I have one additional requirement, which is return Null if N-th largest element does not exist. Any thoughts are appreciated.

thanks in advance,
Lin

Answer

You could do this:

SELECT t1.*
FROM (
  SELECT *
  FROM my_table
  ORDER BY value DESC
  LIMIT 1
  OFFSET N -- Set your value for N here, N being 0-based
) t1
RIGHT OUTER JOIN (
  SELECT null -- This will guarantee that you have at least one row
) t2
ON TRUE