# SQL query to find second highest salary?

Consider below simple table:

Name Salary --------------- abc 100000 bcd 1000000 efg 40000 ghi 500000

How to find the employee whose salary is second highest. For example, in above table, “ghi” has the second highest salary as 500000.

Below is simple query to find the employee whose salary is highest.

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);

There are other ways also as suggested by RajnishKrJha.

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

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

One way as suggested by **Arka Poddar**.

IN SQL Server using Common Table Expression or **CTE**, we can find the second highest salary:

WITH T AS ( SELECT * DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk FROM Employees ) SELECT Name FROM T WHERE Rnk=2;

**How to find the third largest salary?**

Simple, we can do one more nesting.

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

Note that instead of nesting for second, third largest salary, we can find nth salary using general query like in mysql:

select salary from employee order by salary desc limit n-1,1; Or Select name,salary from employee A where n-1 = (Select count(1) from employee B where B.salary>A.salary)

This article is contributed by Kartik. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

## Recommended Posts:

- DBMS | How to find the highest normal form of a relation
- Query to find 2nd largest value in a column in Table
- SQL | Query Processing
- SQL | SELECT Query
- Query Optimization
- Structured Query Language (SQL)
- DBMS | Join operation Vs nested query
- Data Mining | Set 2
- Different types of MySQL Triggers (with examples)
- Different types of Procedures in MySQL
- Introduction to Large Objects (LOBs)
- How to implement SQL GROUP BY in Java?
- Difference between LONGs vs LOBs
- Large objects(LOBs) for Semi Structured and Unstructured Data