检索第n条记录

Question

我有一个工资表，我想找第二大工资记录，第三大工资记录等等。为了检索第二大记录，我使用以下查询

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

同样，我如何找到第三大记录或第四大记录等等？有没有办法检索特定的记录？

Answer 1

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)

Answer 2

您可以使用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)

Answer 3

使用RANK()函数

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