SQL query to find second highest salary

SQL query to find second highest salary

SQL query to find second highest salary

To find the Nth 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.

To find first and second max salary from employee

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)

 

2020-03-05