How to find find second highest salary in SQL Server?

How to find find second highest salary in SQL Server?

To find the 2nd highest salary is a very common interview question if you are going for the role at the junior level. Also, understand how this SQL query works.

 

Sample table and data for Nth Highest Salary Problem

CREATE TABLE Employee (name varchar(10), salary int); 
inserting sample data into Employee table INSERT INTO Employee VALUES ('O', 3000);
INSERT INTO Employee VALUES ('A', 5000);
INSERT INTO Employee VALUES ('B', 4000);
INSERT INTO Employee VALUES ('C', 6000);
INSERT INTO Employee VALUES ('D', 8000);
SELECT salary FROM (
    SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC
) WHERE rownum<=2

 

SELECT name, salary FROM #Employee e1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2 WHERE e2.salary > e1.salary)

 

Select max(sal) from table_name where sal<(select max(sal)from table_name)

 

2021-10-03