SQL 查询以查找列的第 n 个最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/80706/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to find nth max value of a column
提问by kiritsinh parmar
I want to find 2nd,3rd..nth maximum value of a column
我想找到列的第 2、3..n 个最大值
采纳答案by TK.
You could sort the column into descending format and then just obtain the value from the nth row.
您可以将列排序为降序格式，然后从第 n 行获取值。
EDIT::
编辑：：
Updated as per comment request. WARNINGcompletely untested!
根据评论请求更新。警告完全未经测试！
SELECT DOB FROM (SELECT DOB FROM USERS ORDER BY DOB DESC) WHERE ROWID = 6
Something like the above should work for Oracle ... you might have to get the syntax right first!
像上面这样的东西应该适用于 Oracle ......您可能必须首先获得正确的语法！
回答by dexter
Consider the following Employee table with a single column for salary.
考虑下面的 Employee 表，其中只有一个列是薪水。
+------+ | Sal | +------+ | 3500 | | 2500 | | 2500 | | 5500 | | 7500 | +------+
The following query will return the Nth Maximum element.
以下查询将返回第 N 个最大值元素。
select SAL from EMPLOYEE E1 where
(N - 1) = (select count(distinct(SAL))
from EMPLOYEE E2
where E2.SAL > E1.SAL )
For eg. when the second maximum value is required,
例如。当需要第二个最大值时，
select SAL from EMPLOYEE E1 where
(2 - 1) = (select count(distinct(SAL))
from EMPLOYEE E2
where E2.SAL > E1.SAL )
+------+ | Sal | +------+ | 5500 | +------+
回答by Pieter
You didn't specify which database, on MySQL you can do
你没有指定哪个数据库，在 MySQL 上你可以做
SELECT column FROM table ORDER BY column DESC LIMIT 7,10;
Would skip the first 7, and then get you the next ten highest.
将跳过前 7 个，然后让您获得下一个最高的 10 个。
回答by Pieter
Again you may need to fix for your database, but if you want the top 2nd value in a dataset that potentially has the value duplicated, you'll want to do a group as well:
同样，您可能需要修复您的数据库，但如果您希望数据集中的第 2 个值可能具有重复的值，您还需要做一个组：
SELECT column
FROM table
WHERE column IS NOT NULL
GROUP BY column
ORDER BY column DESC
LIMIT 5 OFFSET 2;
Would skip the first two, and then will get you the next five highest.
会跳过前两个，然后会让你获得接下来的五个最高的。
回答by Matt Rogish
Pure SQL (note: I would recommend using SQL features specific to your DBMS since it will be likely more efficient). This will get you the n+1th largest value (to get smallest, flip the <). If you have duplicates, make it COUNT( DISTINCT VALUE )..
纯 SQL（注意：我建议使用特定于您的 DBMS 的 SQL 功能，因为它可能更有效）。这将为您提供第 n+1 个最大值（要获得最小值，请翻转 <）。如果您有重复项，请将其设为 COUNT( DISTINCT VALUE )。
select id from table order by id desc limit 4 ;
+------+
| id |
+------+
| 2211 |
| 2210 |
| 2209 |
| 2208 |
+------+
SELECT yourvalue
FROM yourtable t1
WHERE EXISTS( SELECT COUNT(*)
FROM yourtable t2
WHERE t1.id <> t2.id
AND t1.yourvalue < t2.yourvalue
HAVING COUNT(*) = 3 )
+------+
| id |
+------+
| 2208 |
+------+
回答by German Alex
(Table Name=Student, Column Name= mark)
（表名=学生，列名=标记）
select * from(select row_number() over (order by mark desc) as t,mark from student group by mark) as td where t=4
回答by Abhishek B Patel
You can find the nth largest value of column by using the following query:
您可以使用以下查询找到列的第 n 个最大值：
SELECT * FROM TableName a WHERE
n = (SELECT count(DISTINCT(b.ColumnName))
FROM TableName b WHERE a.ColumnName <=b.ColumnName);
回答by rashedcs
select column_name from table_name
order by column_name desc limit n-1,1;
where n = 1, 2, 3,....nth max value.
其中 n = 1, 2, 3,....nth 个最大值。
回答by mjp
mysql query: suppose i want to find out nth max salary form employee table
mysql 查询： 假设我想从员工表中找出第 n 个最高工资
select salary
form employee
order by salary desc
limit n-1,1 ;
回答by Prashant Maheshwari Andro
This is query for getting nth Highest from colomn put n=0 for second highest and n= 1 for 3rd highest and so on...
这是从 colomn 中获取第 n 个最高的查询 put n=0 表示第二高，n=1 表示第三高，依此类推...
SELECT * FROM TableName
WHERE ColomnName<(select max(ColomnName) from TableName)-n order by ColomnName desc limit 1;
