SQL Server: Find Nth Highest Salary value

Execute below scripts to create Employee table

CREATE TABLE Employee (ID INT,emp_name VARCHAR(50),Salary INT)

INSERT INTO Employee VALUES(1,'Dolu',15000)
INSERT INTO Employee VALUES(2,'Bolu',15000)
INSERT INTO Employee VALUES(3,'Kalia',10000)
INSERT INTO Employee VALUES(4,'Bheem',50000)
INSERT INTO Employee VALUES(5,'Krishna',40000)
INSERT INTO Employee VALUES(6,'Chutki',30000)

SELECT * FROM Employee

ID	emp_name	salary
1	Dolu	15000
2	Bolu	15000
3	Kalia	10000
4	Bheem	50000
5	Krishna	40000
6	Chutki	30000

Execute any one of the queries to obtain high salaried employee

SELECT TOP 1 * FROM Employee WHERE salary IN (SELECT TOP 1 salary FROM Employee ORDER BY salary DESC)(or)
SELECT TOP 1 * FROM (SELECT TOP 1 * FROM Employee ORDER BY salary DESC )AS B ORDER BY B.salary

To obtain 2nd highest salaried employee we can to execute

SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Employee ORDER BY salary DESC )AS B ORDER BY B.salary

To obtain 3rd highest salaried employee we can execute

SELECT TOP 1 * FROM (SELECT TOP 3 * FROM Employee ORDER BY salary DESC )AS B ORDER BY B.salary

And to obtain nth highest salaried employee just exucute

SELECT TOP 1 * FROM (SELECT TOP n * FROM Employee ORDER BY salary DESC )AS B ORDER BY B.salary

 To obtain multiple employees who has same highest salaries then execute

SELECT Top 1 WITH TIES emp_name, salary from Employee order by salary desc

Note

Do not use the query SELECT TOP 1 MAX(salary),emp_name FROM Employee  GROUP BY emp_name
This will end up with wrong results.

Nth Lowest Salary value

To obtain Lowest salaried employee we can to execute

SELECT TOP 1 * FROM (SELECT TOP 1 * FROM Employee ORDER BY salary ASC)AS B ORDER BY B.salary DESC

To obtain 2nd lowest salaried employee

SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Employee ORDER BY salary ASC)AS B ORDER BY B.salary DESC