Sunday, May 23, 2010

Nth Highest Value (Eg.Salary)

/*
------------------------------------------------------------------
GETTING THE Nth HIGHEST SALARY/NUMBERS ETC. ALONG WITH THE DETAILS :
------------------------------------------------------------------
*/


-----------Method 1----------

Select * from Table1( Select min(Column_1) from Table1 where Column_1 in
            (Select distinct top n from Table1 order by Column_1 desc)
             )
--Example--
Select * from Erecords Where Esal in (select min(ESal) from ERecords where ESal in
(select distinct top 2 ESal from ERecords order by ESal desc))


-----------Method 2----------

Select * From Table1 [alias_1] Where
(2 = (Select Count(Distinct [ESal]) From Table1 [alias_2] where [alias_1].Table1 <= [alias_2].Table1))




--Example--
 
Select * From [Employee_Details] [E1] Where
(2 = (Select Count(Distinct [ESal]) From [Employee_Details] [E2] where [E1].[ESal] <= [E2].[ESal]))