在SQL中找到第N个最大元素 [英] find the N-th largest element in SQL

问题描述

想知道如何编写SQL函数以查找表中第N个最大的元素，如果没有第N个最大的元素，则返回Null.

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.

使用MySQL/MySQL工作台.

Using MySQL/MySQL workbench.

顺便说一句，我的问题与第N个最高薪水问题不同，因为我还有一个附加要求，如果第N个最大元素不存在，则返回Null.任何想法都值得赞赏.

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.

先谢谢了， 林

推荐答案

您可以执行以下操作:

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

这篇关于在SQL中找到第N个最大元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！