Thursday, August 9, 2012

SQL SERVER – Query to Retrieve the Nth Maximum Value

Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined.

Method :1

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)




In the above example, the inner query uses a value of the outer query in its filter condition meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row.

Reference : Pinal Dave (http://blog.SQLAuthority.com)


Method:2

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

Method : 3

select a.Salary from(
select top 3 ROW_NUMBER() OVER(ORDER BY Salary DESC) AS 'ROW_NUMBER',Salary from Employee order by Salary desc
)a where a.ROW_NUMBER=3









If you are searching life partner. your searching end with kpmarriage.com. now kpmarriage.com offer free matrimonial website which offer free message, free chat, free view contact information. so register here : kpmarriage.com- Free matrimonial website