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
精彩评论