SQL 如何从表中获取第二大或第三大条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4483222/
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
How to get second largest or third largest entry from a table
提问by Ninja
Can anyone please tell me how to find out the N th largest entry from a tablein Oracle?
谁能告诉我如何从Oracle的表中找出第N 个最大的条目？
Like for largest we can useMAX(column_name) is there any efficient way to find nth largest also?
像最大我们可以使用MAX(column_name)是否有任何有效的方法来找到第 n 个最大？
回答by a_horse_with_no_name
SELECT *
FROM (
SELECT some_column,
row_number() over (order by your_sort_column desc) as row_num
FROM some_table
) t
WHERE row_num = 3
If you expect more than one row to have the same value in your_sort_columnyou can also use the rank() function
如果您希望多于一行具有相同的值，your_sort_column您还可以使用 rank() 函数
SELECT *
FROM (
SELECT some_column,
rank() over (order by your_sort_column desc) as row_rank
FROM some_table
) t
WHERE row_rank = 3
这可能会返回不止一行..回答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 prakash
I think the below query will work to find second highest record with NOT IN .
我认为下面的查询可以找到 NOT IN 的第二高记录。
SELECT MAX( userId)FROM table WHERE userIdNOT IN (SELECT MAX( userId)FROM table)
SELECT MAX( userId)FROM table WHERE userIdNOT IN (SELECT MAX( userId)FROM table)
simple and useful...
简单实用...
回答by Sanjeev Yadav
To get second largest salary use this:
要获得第二大薪水，请使用以下命令：
select salary from
(select s2.salary,rownum rm from
(select distinct salary from employees order by salary desc)
s2 where rownum<=2)
where rm >= 2
回答by codegeek
It works for second highest salary,
它适用于第二高的工资，
$query = "SELECT * FROM `table_name` ORDER BY field_name` DESC LIMIT 1 , 1 ";
回答by sudhakar reddy karukala
Try this,
尝试这个，
SELECT Sal FROM Tab ORDER BY Sal DESC LIMIT 2,1
回答by user2326989
Try this:
尝试这个：
SELECT DISTINCT TOP 3 id,[Password]
FROM Users_changepassword
WHERE [UserId] = 3
ORDER BY id DESC
回答by UserszrKs
You can try this sql where Row_number() function of the oracle sql is used
你可以试试这个sql where使用oracle sql的Row_number()函数
select column_name from (
select column_name ,
row_number() over (order by column_name desc) as row_num
from table_Name ) tablex
where row_num =3
回答by Piyush
SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)
回答by Lukasz Szozda
You could use CONNECT BY PRIORby:
您可以CONNECT BY PRIOR通过以下方式使用：
CREATE TABLE t(i INT, sal INT);
INSERT INTO t(i, sal)
SELECT 1,100 FROM dual UNION
SELECT 2,100 FROM dual UNION
SELECT 3,200 FROM dual UNION
SELECT 4,500 FROM dual UNION
SELECT 5,1000 FROM dual;
Query:
询问：
SELECT level, MAX(sal) AS sal
FROM t
--WHERE level = 2 -- set position here
CONNECT BY prior sal > sal
GROUP BY level
ORDER BY level;
EDIT:
编辑：
Second approach is to use NTH_VALUEanalytic function:
第二种方法是使用NTH_VALUE解析函数：
SELECT DISTINCT NTH_VALUE(sal, 2) OVER(ORDER BY sal DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM t;
