SQL - How to find 2nd highest salary from the table employee?.

Answers collected from the below site.Please refer to the site for details.

www.sqllion.com-dense_rank

select * from (

select DENSE_RANK() OVER(partition by DeptNo order by salary desc) as RankID,* from dbo.employee) InnQ

where InnQ.RankID = 2

www.questpond.com

(Using corelated queries)

select  * from Empsal MainTable 

where 2 = (select count(*) from Empsal Auxillary where Auxillary.salary >= MainTable.salary)

----------------------------------------------------------------------------------------------------------------------------------

Reference:

Explain RowNumber,Partition,Rank and DenseRank ?

Refer the above video for the details.

 Select ROW_NUMBER( ) OVER(Order By CustomerName) as OrderNumber,
ROW_NUMBER( ) OVER( PARTITION By VendorName Order By VendorName) as VendorNumber,
DENSE_RANK( ) OVER( Order by CustomerName) as CustomerNumber,
CustomerName,ProductName,Amount,VendorName From Sales

note:If Rank( ) is used in place of  DENSE_RANK( ) , it does not give a sequential number.


 

----------------------------------------------------------------------------------------------------------------------------------