# 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, etc 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
```
• ```SELECT name, salary
FROM employee A
WHERE n-1 = (SELECT count(1)
FROM employee B
WHERE B.salary>A.salary)
```

My Personal Notes arrow_drop_up