Find Second Max value in SQL

There are different ways where we can write query to find second max value in column

 We can write a sub-query to achieve the result
SELECT MAX (SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX (SALARY) FROM EMPLOYEE)

The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.

Second highest salary from emp table for each department

SELECT Max (e1.salary) from tbl_emp e1 where e1.Salary NOT IN (select Max (e2.salary) from tbl_emp e2 group by e2.departId) group by e1.departId

To find 2nd highest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT (DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

Select max(salary) from employee where salary in (select distinct Top 2 salary from employee order by salary desc)

always use indexing to get highest values
best practice to get highest salary or any value is based on Row_number, Dense_rank, rank

WITH T AS
(
SELECT *,
       DENSE_RANK() OVER (ORDER BY ID Desc) AS Rnk
FROM tblUserRelationship
)
SELECT ID
FROM T
WHERE Rnk=3;

I will post the same very soon in brief.
please check other post for the ranking operation