在表SQL中查找第二个最大值 [英] Find Second Max value in the Table SQL

问题描述

ID名称价格

1 ram 1000

2 ram 3000

3 raj 2000

4 krish 1000

5 sai 8000

6 raj 6000

7 ram 2000



In那张桌子，我想找出'ram'的第二个最高价格。

如何找到它。



先谢谢你

ID Name price
1 ram 1000
2 ram 3000
3 raj 2000
4 krish 1000
5 sai 8000
6 raj 6000
7 ram 2000

In that table , i want to find out the second maximum price for 'ram'.
How to find it.

Thanks in Advance

推荐答案

像这样：

Like this:

SELECT MAX(Price) FROM MyTable
WHERE Price NOT IN (SELECT MAX(Price) FROM MyTable WHERE Name = 'Ram')
AND  Name = 'Ram'

尝试：

Try:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.price DESC)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 2

这是另一个，使用top：

Here is another one, using top:

select top 1 *
from (
    select top 2 *
    from theTable
    where lower(name) = 'ram'
    order by price desc
) MaxPriceTop2
order by price asc
;

另一个使用OFFSET FETCH子句，MSSqlServer 2012+：

And another one using the OFFSET FETCH clause, MSSqlServer 2012+:

select *
from theTable
where lower(name) = 'ram'
order by price desc
    offset 1 rows
    fetch next 1 rows only
;

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