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 )

[QUOTE=kumar.santosh;12845]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[/QUOTE]

Can You Try this?
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.

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)); :eek: 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.

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

[QUOTE]Barbie's query would give the correct reslt [/QUOTE]

Sutnarcha , barbies query will give you correct results no doubt .
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. :eek: Thanx for helping me see the difference.

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

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...:-)

[QUOTE=kumar.santosh;12845]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[/QUOTE]

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