I want to find the 2nd highest number from a list by sql queries.
i.e What is the query to find the 2nd highest "UnitPrice" from "Products" table in "Northwind" database
Find 2nd Highest in sql server
I want to find the 2nd highest number from a list by sql queries.
i.e What is the query to find the 2nd highest "UnitPrice" from "Products" table in "Northwind" database
select max(UnitPrice)
from Products
where UnitPrice < (select max(UnitPrice)
from Products )
Can You Try this?Originally Posted by kumar.santosh
Select Top 1 Unitprice from(select Top 2 Unitprice from Products Order by Unitprice desc) Products Order by Unitprice
Barbie's query would give the correct reslt
vcyogi's idea is also correct, but the portion of the command is not required.
It can be changed as,
SELECT TOP 1 UnitPrice FROM (SELECT TOP 2 UnitPrice FROM Products ORDER BY UnitPrice DESC);
Here, the inner query gives only 2 records where UnitPrice is 1st highest and 2nd highest in DECD order, which means the record with 2nd highest UnitPrice comes at the top. Now, the outer query select only the top one.
Lack of WILL POWER has caused more failure than
lack of INTELLIGENCE or ABILITY.
-sutnarcha-
Can you retrive more than one 2'nd highest salary?? example emp table ename salary aaaa 1000 bbbb 2000 cccc 3000 dddd 2000 can you retrive two records 'bbbb' & 'dddd' for 2'nd highest salary in a single query??
Select * from emp where sal= (select max(sal) from emp where sal< (select max(sal) from emp));this looks like the same command put multiple times between = and <.:d but it works.
this is an indirect method. There can be a direct query for the same. I will have to come back to this thread with it.
Lack of WILL POWER has caused more failure than
lack of INTELLIGENCE or ABILITY.
-sutnarcha-
I find the Query using WITH TIES keyword in Sql
select top 1 with ties eno from (select top 2 with ties eno
from emp order by eno desc) emp order by eno
Note:With Ties Returns the similar record of resulted record
Sutnarcha , barbies query will give you correct results no doubt .Barbie's query would give the correct reslt
But VCyogi has given you a generic / more flexible query with wich you can get n-th higest / lowset salaray. It is just the matter of changing the n value in inner query and altering the sort order.
Hay, that's good man !!!
Keep up the spirit VCYogi
SQLTweety,
I know see that VCYogi's query is more flexible.
Lack of WILL POWER has caused more failure than
lack of INTELLIGENCE or ABILITY.
-sutnarcha-
SELECT top 1 UnitPrice FROM (SELECT TOP 2 UnitPrice FROM Products ORDER BY UnitPrice DESC)
SELECT UnitPrice where rownum<2 FROM (SELECT TOP 2 UnitPrice FROM Products ORDER BY UnitPrice );
select min(unit price ) from products where unitprice in (select top 2 unit price from products order by unitprice desc)
Very helpful thread...Now, I learned two new sql commands...TIE and TOP...Thanks
Regards,
Anoop :)
If its useful, dont forget to [COLOR="Red"]THANK[/COLOR] me :cool:
Select Top N sal from employee where sal in (select distinct sal from employee order by sal desc)
In the above query N represents the nth highest salary.. I think this help ur problem easily...:-)
select top 1 * from (select top 2 * from Products order by UnitPrice desc) table1 order by Id asc.....
Select top 1 price from (select top 2 price from products order by price desc);
select * from ( select dense_rank() over( order by unitprice desc ) as rownum, * from products ) p where p.rownum = 2
Posting Permissions
Reply With Quote