SELECT name, salary
FROM #Employee e1
WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
WHERE e2.salary > e1.salary)
for the 2nd maximum you can replace N with 2, and for 3rd maximum replace N with 3, here is the output:

SELECT name, MAX(salary) as salary FROM employee
We can nest the above query to find the second largest salary.

SELECT name, MAX(salary) AS salary
FROM employee
WHERE salary < (SELECT MAX(salary)
FROM employee);