Find out 2nd,3rd and N th maximum value in a table

Solution to finding the 2nd highest salary:

Select MAX(Salary) From Employee

Where Salary Not In (Select Max(Salary) From Employee

 

Using not equal to operator:

select MAX(Salary) from Employee

WHERE Salary <> (select MAX(Salary) from Employee )

 

 

To finding the nth highest salary

 

SELECT *

FROM Employee Emp1

WHERE (N-1) = (

SELECT COUNT(DISTINCT(Emp2.Salary))

FROM Employee Emp2

WHERE Emp2.Salary > Emp1.Salary)

Using order by: 

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