# SQL query to find second highest salary?

• Difficulty Level : Medium
• Last Updated : 10 Oct, 2022

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 *from employee where salary=(select Max(salary) from employee);`

Depending on the default settings and MySQL version, we may receive ERROR 1140 when running this query on the MySQL database. The solution can be found in the article’s final section.

We can nest the above query to find the second largest salary.

```select *from employee
group by salary
order by  salary desc limit 1,1;```

There are other ways :

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

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

If multiple employee have same salary.
Suppose you have to find 4th highest salary

```SELECT * FROM employee
WHERE salary= (SELECT DISTINCT(salary)
FROM employee ORDER BY salary LIMIT 3,1);```

Generic query will be

```SELECT * FROM employee
WHERE salary= (SELECT DISTINCT(salary)
FROM employee ORDER BY salary DESC LIMIT n-1,1);```

Solution for ERROR 1140 :

While querying the database to fetch an employee with maximum salary, we might get ERROR 1140:

```mysql> SELECT name, MAX(salary) AS salary FROM employee;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column
'database.employee.name'; this is incompatible with sql_mode=only_full_group_by```

We can clearly understand from the error message that our query is an aggregation query from ‘MAX(salary)’ and at the same time, it uses an unaggregated column ‘name’, which creates ambiguity for MySQL.  This error will be captured by those versions of MySQL which contain the value ‘only_full_group_by’ in the ‘sql_mode’ variable. We can check this variable in MySQL using the following command.

`SHOW VARIABLES LIKE "sql_mode";`

To avoid confusion, we must avoid using the aggregated column and the unaggregated column in the same query to eliminate this error. The following command would help in this.

```// EMPLOYEE WITH HIGHEST SALARY
SELECT name, salary FROM employee ORDER BY salary DESC LIMIT 1;

// EMPLOYEE WITH SECOND HIGHEST SALARY
SELECT name, salary FROM employee WHERE salary < (SELECT MAX(salary) FROM employee) ORDER BY sal DESC LIMIT 1;

// EMPLOYEE WITH Nth HIGHEST SALARY
SELECT name, salary FROM employee ORDER BY salary DESC LIMIT (N-1), 1;```

This Solution is provided by Mohit.