Get the nth highest salary using Sql Server 2005
CREATE TABLE #Employees (EmpID int, EmpName varchar(10), Salary int) INSERT #Employees SELECT 1,'Tim',345345 INSERT #Employees SELECT 2,'Jill',76845 INSERT #Employees SELECT 3,'Kathy',234545 INSERT #Employees SELECT 4,'Jack',74564 INSERT #Employees SELECT 5,'Harry',56756456 INSERT #Employees SELECT 6,'Arnol',75675 INSERT #Employees SELECT 7,'Dave',76566 INSERT #Employees SELECT 8,'Zinnade',58776 INSERT #Employees SELECT 9,'Iflar',345567 INSERT #Employees SELECT 10,'Jimmy',76766 SELECT EMPNAME,SALARY, ROW_NUMBER() OVER(ORDER BY SALARY DESC)AS TOPSALARY FROM #Employees SELECT EmpName, Salary FROM ( SELECT EMPNAME,SALARY, ROW_NUMBER() OVER(ORDER BY SALARY DESC)AS TOPSALARY FROM #Employees)TMP WHERE TOPSALARY=5 DROP TABLE #Employees