[go: up one dir, main page]
Scribd
Upload
16 views5 pages

SQL Query To Find Second Highest Salary - GeeksforGeeks

The document provides SQL queries to find the second highest salary from a sample employee table, highlighting various methods including nested queries and Common Table Expressions (CTE). It also addresses potential errors in MySQL related to aggregation and offers solutions to avoid them. Additionally, it mentions how to find the nth highest salary and includes examples for handling cases with duplicate salaries.

Uploaded by

RM
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views5 pages

SQL Query To Find Second Highest Salary - GeeksforGeeks

The document provides SQL queries to find the second highest salary from a sample employee table, highlighting various methods including nested queries and Common Table Expressions (CTE). It also addresses potential errors in MySQL related to aggregation and offers solutions to avoid them. Additionally, it mentions how to find the nth highest salary and includes examples for handling cases with duplicate salaries.

Uploaded by

RM
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

SQL Query to Find Second

Highest Salary
There are a lot of real-world scenarios where a query for finding the
nth salary will be really useful. Considering finding the second-
highest salary in SQL, we have one sample table.

Consider below simple table:

Name Salary
Aman 100000
Shubham 1000000
Naveen 40000
Nishant 500000

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

Below is a simple query to find the employee whose salary is the


highest.

select * from employee where salary=(select Max(salary)


from employee);

Note: 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 SQL query to find the second-largest


salary.

select *from employee


group by salary
order by salary desc limit 1,1;

Output:
Name Salary
Nishant 500000

Other Ways to Find Second Highest Salary in SQL

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 ASd
(
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 the second, third, etc largest salary,
we can find the nth salary using a 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 employees have the same salary:

Suppose you have to find 4th highest salary

SELECT * FROM employee


WHERE salary= (SELECT DISTINCT(salary)
FROM employee ORDER BY salary DESC LIMIT 3,1);
The 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 salary DESC LIMIT 1;

// EMPLOYEE WITH Nth HIGHEST SALARY


SELECT name, salary FROM employee ORDER BY salary DESC
LIMIT (N-1), 1;

SQL Interview Question – 2nd Highest Salary

1. 2nd Highest Salary (Top 50 SQL Interview Q…

This Solution is provided by Mohit.


Kartik contributes to this article. Please write comments if you find
anything incorrect or want to share more information about the topic
discussed above.

You might also like