找到第二大值的最简单的SQL查询是什么?

小开

最简单的方法是从应用程序的结果集中获取第二个值:

SELECT DISTINCT value
FROM Table
ORDER BY value DESC
LIMIT 2

但是如果你必须使用SQL选择第二个值，那么:

SELECT MIN(value)
FROM ( SELECT DISTINCT value
FROM Table
ORDER BY value DESC
LIMIT 2
) AS t

小开

我想你可以这样做:

SELECT *
FROM Table
ORDER BY NumericalColumn DESC
LIMIT 1 OFFSET 1

或

SELECT *
FROM Table ORDER BY NumericalColumn DESC
LIMIT (1, 1)

取决于您的数据库服务器。提示:SQL Server不做LIMIT。

小开

最佳答案

SELECT MAX( col )
FROM table
WHERE col < ( SELECT MAX( col )
FROM table )

小开

select top 1 MyIntColumn from MyTable
where
MyIntColumn <> (select top 1 MyIntColumn from MyTable order by MyIntColumn desc)
order by MyIntColumn desc

小开

这在MS SQL工作:

select max([COLUMN_NAME]) from [TABLE_NAME] where [COLUMN_NAME] <
( select max([COLUMN_NAME]) from [TABLE_NAME] )

小开

像这样的东西?不过我还没有测试过:

select top 1 x
from (
select top 2 distinct x
from y
order by x desc
) z
order by x

小开

在T-Sql中有两种方法:

--filter out the max
select max( col )
from [table]
where col < (
select max( col )
from [table] )


--sort top two then bottom one
select top 1 col
from (
select top 2 col
from [table]
order by col) topTwo
order by col desc

在Microsoft SQL中，第一种方法的速度是第二种方法的两倍，即使所讨论的列是集群的。

这是因为与max聚合使用的表或索引扫描相比，排序操作相对较慢。

或者，在Microsoft SQL 2005及以上版本中，你可以使用ROW_NUMBER()函数:

select col
from (
select ROW_NUMBER() over (order by col asc) as 'rowNum', col
from [table] ) withRowNum
where rowNum = 2

小开

看到如何在SQL数据库表中选择第n行?。

Sybase SQL Anywhere支持:

SELECT TOP 1 START AT 2 value from table ORDER BY value

小开

我在这里看到了一些特定于SQL Server的解决方案和一些特定于MySQL的解决方案，所以你可能想要澄清你需要哪个数据库。不过如果我必须猜的话，我会说SQL Server，因为这在MySQL中是微不足道的。

我也看到一些解决方案不会起作用，因为它们没有考虑到重复的可能性，所以要小心接受哪些解决方案。最后，我看到一些将工作，但这将对表进行两次完整的扫描。你要确保第二次扫描只看2个值。

SQL Server(2012年前):

SELECT MIN([column]) AS [column]
FROM (
SELECT TOP 2 [column]
FROM [Table]
GROUP BY [column]
ORDER BY [column] DESC
) a

MySQL:

SELECT `column`
FROM `table`
GROUP BY `column`
ORDER BY `column` DESC
LIMIT 1,1

更新:

SQL Server 2012现在支持更干净(和标准< em > < / em >)的OFFSET/FETCH语法:

SELECT [column]
FROM [Table]
GROUP BY [column]
ORDER BY [column] DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

小开

select * from emp e where 3>=(select count(distinct salary)
from emp where s.salary<=salary)

这个查询选择最多三个工资。如果两个emp得到相同的薪水，这不会影响查询。

小开

使用相关查询:

Select * from x x1 where 1 = (select count(*) from x where x1.a < a)

小开

Tom，相信当select max([COLUMN_NAME]) from [TABLE_NAME]节中有多个返回值时，这将失败。也就是说，在数据集中有2个以上的值。

对您的查询稍加修改即可工作

select max([COLUMN_NAME])
from [TABLE_NAME]
where [COLUMN_NAME] IN ( select max([COLUMN_NAME])
from [TABLE_NAME]
)

小开

select max(COL_NAME)
from TABLE_NAME
where COL_NAME in ( select COL_NAME
from TABLE_NAME
where COL_NAME < ( select max(COL_NAME)
from TABLE_NAME
)
);

子查询返回除最大值以外的所有值。从返回的列表中选择Max值

小开

select min(sal) from emp where sal in
(select TOP 2 (sal) from emp order by sal desc)

请注意

sal is col name
Emp是表名

小开

SELECT MAX(col)
FROM table
WHERE col NOT IN ( SELECT MAX(col)
FROM table
);

小开

select col_name
from (
select dense_rank() over (order by col_name desc) as 'rank', col_name
from table_name ) withrank
where rank = 2

小开


select * from (select ROW_NUMBER() over (Order by Col_x desc) as Row, Col_1
from table_1)as table_new tn inner join table_1 t1
on tn.col_1 = t1.col_1
where row = 2

希望这有助于获得任何行.....的值

小开

SELECT
*
FROM
table
WHERE
column < (SELECT max(columnq) FROM table)
ORDER BY
column DESC LIMIT 1

小开

查找第二大值的一个非常简单的查询

SELECT `Column`
FROM `Table`
ORDER BY `Column` DESC
LIMIT 1,1;

小开

您可以使用以下查询来查找列的第二大值

SELECT *
FROM TableName a
WHERE
2 = (SELECT count(DISTINCT(b.ColumnName))
FROM TableName b WHERE
a.ColumnName <= b.ColumnName);

你可以在下面的链接中找到更多的细节

http://www.abhishekbpatel.com/2012/12/how-to-get-nth-maximum-and-minimun.html < a href = " http://www.abhishekbpatel.com/2012/12/how-to-get-nth-maximum-and-minimun.html " > < / >

小开

查询一行中第2大的数字

select Top 1 (salary) from XYZ
where Salary not in (select distinct TOP 1(salary) from XYZ order by Salary desc)
ORDER BY Salary DESC

通过将突出显示的Top 1更改为TOP 2， 3或4，你可以分别找到第三，第四和第五高的。

小开

我们还可以像下面这样使用order by和top 1元素:

Select  top 1 col_name from table_name
where col_name < (Select top 1 col_name from table_name order by col_name desc)
order by col_name desc

小开

SELECT * FROM EMP
WHERE salary=
(SELECT MAX(salary) FROM EMP
WHERE salary != (SELECT MAX(salary) FROM EMP)
);

小开

这是求列中第二大值的另一种方法。考虑表'Student'和列'Age'。那么问题是，

select top 1 Age
from Student
where Age in ( select distinct top 2 Age
from Student order by Age desc
) order by Age asc

小开

这是最漂亮的方式:

SELECT
Column name
FROM
Table name
ORDER BY
Column name DESC
LIMIT 1,1

小开

试一试:

select a.* ,b.* from
(select * from (select ROW_NUMBER() OVER(ORDER BY fc_amount desc) SrNo1, fc_amount as amount1 From entry group by fc_amount) tbl where tbl.SrNo1 = 2) a
,
(select * from (select ROW_NUMBER() OVER(ORDER BY fc_amount asc) SrNo2, fc_amount as amount2  From entry group by fc_amount) tbl where tbl.SrNo2 =2) b

小开

select * from [table] where (column)=(select max(column)from [table] where column < (select max(column)from [table]))

小开

使用这个查询。

SELECT MAX( colname )
FROM Tablename
where colname < (
SELECT MAX( colname )
FROM Tablename)

小开

select MAX(salary) as SecondMax from test where salary !=(select MAX(salary) from test)

小开

select age
from student
group by id having age< ( select max(age)
from student
)
order by age
limit 1

小开

SELECT MAX(Salary)
FROM Employee
WHERE Salary NOT IN ( SELECT MAX(Salary)
FROM Employee
)

这个查询将返回结果中的最高工资，结果中不包含整个表中的最高工资。

小开

最简单的

select sal
from salary
order by sal desc
limit 1 offset 1

小开

select score
from table
where score = (select max(score)-1 from table)

小开

Microsoft SQL Server -对第n个最高值(别名子查询)使用两个top。

求第2高:

SELECT TOP 1 q.*
FROM (SELECT TOP 2 column_name FROM table_name ORDER BY column_name DESC) as q
ORDER BY column_name ASC;

使用TOP两次，但需要一个别名子查询。本质上，内部查询按降序取最大的2个值，然后外部查询按升序翻转，以便第2高现在在顶部。SELECT语句返回这个顶部。

为了求解第n个最大值，修改子查询TOP值。例如:

SELECT TOP 1 q.*
FROM (SELECT TOP 5 column_name FROM table_name ORDER BY column_name DESC) as q
ORDER BY column_name;

将返回第5大值。

小开

我知道这是个老问题，但这给了我一个更好的执行计划:

 SELECT TOP 1 LEAD(MAX (column)) OVER (ORDER BY column desc)
FROM TABLE
GROUP BY column

小开

select extension from [dbo].[Employees] order by extension desc offset  2  rows fetch next  1 rows only

小开

正如你提到的重复值。在这种情况下，你可以使用截然不同的和集团来找出第二高的值

这是一张表格

工资

：

集团

SELECT  amount FROM  salary
GROUP by amount
ORDER BY  amount DESC
LIMIT 1 , 1

截然不同的

SELECT DISTINCT amount
FROM  salary
ORDER BY  amount DESC
LIMIT 1 , 1

LIMIT =起始索引的第一部分

LIMIT的第二部分=多少值

小开

SELECT MAX(sal)
FROM emp
WHERE sal NOT IN ( SELECT top 3 sal
FROM emp order by sal desc
)

这将返回emp表中第三高的sal

小开

< p >非常简单。 distinct关键字也会处理重复的关键字

SELECT distinct SupplierID FROM [Products] order by SupplierID desc limit 1 offset 1

小开

从SQL表中获取倒数第二行的最简单方法是使用ORDER BYColumnNameDESC并设置LIMIT 1,1。

试试这个:

SELECT * from `TableName` ORDER BY `ColumnName` DESC LIMIT 1,1

小开

这是一个非常简单的代码，你可以试试这个:-

< p >交货: 表名称= test

salary


1000
1500
1450
7500

MSSQL代码获得第二大值

select salary from test order by salary desc offset 1 rows fetch next 1 rows only;

这里'偏移1行'意味着表的第二行和'只取下1行'是只显示这1行。如果你不使用“fetch next 1 rows only”，那么它会显示从第二行开始的所有行。

小开

该软件

SELECT  *
FROM [Users]
order by UserId desc OFFSET 1 ROW
FETCH NEXT 1 ROW ONLY;

MySQL

SELECT  *
FROM Users
order by UserId desc LIMIT 1 OFFSET 1

不需要子查询…只需跳过一行，然后按降序选择第二行

小开

 SELECT  * FROM `employee` WHERE  employee_salary = (SELECT employee_salary
FROM`employee` GROUP BY employee_salary ORDER BY employee_salary DESC LIMIT
1,1)

小开

select max(column_name)
from table_name
where column_name not in ( select max(column_name)
from table_name
);

不是在是排除column_name的最高值的条件。

参考:程序员面试

小开

您可以使用以下查询找到第n个最大值。

    select top 1 UnitPrice from (select distinct top n UnitPrice from
[Order Details] order by UnitPrice desc) as Result order by UnitPrice asc

这里，n的值将是1(最高的数字)，2(第二高的数字)，3(第三高的数字)……

小开

首先制作一个没有最高工资的虚拟表，然后从虚拟表中查询最高工资

SELECT max(salary) from (Select * FROM emp WHERE salary<> (SELECT MAX(salary) from emp)) temp