Retrieving n'th record

I have a salary table, were I want to find the 2nd largest salary record, 3rd largest salary record and so on. To retrieve 2nd largest r开发者_如何学运维ecord I use the following query

 Select Top 1 * from SalaryTable
  where salary < (Select Max(Salary) from SalaryTable)
  order by Salary desc

Likewise, how can I find 3rd largest record or fourth largest record and so on? Is there a way to retrieve the specific records?

you can get using RANK () function in SQL Server

;WITH CTE AS
(
SELECT ..., RANK() OVER (ORDER BY emp_salary) AS rn
FROM myTable
)
SELECT ...
FROM CTE
WHERE rn = n -- (value of should be replace with numberic number for ex. 1, 2, 3)

Use RANK() function

SELECT * 
  FROM 
        (SELECT *
                ,RANK() OVER (ORDER BY salary) AS SalRnk 
         FROM SalaryTable) AS tblSal      
 WHERE tblSal.SalRnk   = 2 -- for second highest record. change this value to 1,2,3,4 etc... for various rank records

http://msdn.microsoft.com/en-us/library/ms189798.aspx

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)

You can put any value instead of n, it gives you required largest salary.

select top 1 salary 
from(Select Distinct top n salary from Salary order by desc)a 
order by salary Asc