How To Find Second Highest Salary in SQL – The Complete Guide
Finding the second-highest salary is an important task for many companies and organizations. It can help to understand the pay structure in a company and provide insight into how to best reward certain employees. But what does this have to do with SQL? With the power of Structured Query Language (SQL), it is easy to find just about any information related to your database tables.
In this article, we’ll explore some of the different ways you can use SQL queries and subqueries to retrieve data on the second highest salary in SQL.
Understanding Basic SQL Commands
You should have a fair knowledge of basic SQL commands before you go ahead to find the second highest salary in SQL. Some of these basic commands are:
1. Create Database
The first step is to create a database if you haven’t already done so. The syntax for the same is:
CREATE DATABASE database_name;
2. Create a Table
After creating a database, create a table to insert the data. The syntax is:
CREATE TABLE table_name
(
column1_name_constraint,
…
)
3. INSERT Statement
This command is used to enter new entries into the table. The syntax is:
INSERT (column 1, column 2, column 3) INTO table name;
4. SELECT Clause
This is used to find and extract data from one or more tables. The syntax is:
FROM table name, SELECT column1 name, column2 name
5. WHERE Clause
It is used to extract only the data that fulfills a specific condition. The syntax is:
SELECT column1_name FROM table_name WHERE condition
6. UPDATE Statement
This command is used to update, change, and remove the contents of a table. The syntax is:
UPDATE table_name
SET column1_name = value 1…
WHERE condition1, condition2…
To learn more about SQL and what commands to use, you can pursue an SQL course.
Query to Find Second Highest Salary in SQL
Now, let’s see how to get second highest salary in SQL.
The first step is to create a table.
— create a table named employees
CREATE TABLE employees (
name TEXT NOT NULL,
salary INTEGER NOT NULL
);
The next step is to insert some data in the table created.
— insert data into employees table
INSERT INTO employees VALUES (‘Batman’, 20000);
INSERT INTO employees VALUES (‘Superman’, 50000)
INSERT INTO employees VALUES (‘Spiderman’, 70000);
INSERT INTO employees VALUES (‘Aquaman’, 60000)
INSERT INTO employees VALUES (‘Green Arrow’, 40000);
Output:
| Name | Salary |
|---|---|
| Batman | 20000 |
| Superman | 50000 |
| Spiderman | 70000 |
| Aquaman | 60000 |
| Green Arrow | 40000 |
A simple SQL query for the second highest salary is:
Select * from employees where salary=(select Max(salary) from employee);
We can also nest the above query to find the second highest salary in SQL.
select *from employees
group by salary
order by salary desc limit 1,1;
Output:
| Name | Salary |
|---|---|
| Aquaman | 60000 |
Other SQL Query for Second Highest Salary
Let’s look at some other ways through which you can find the second highest salary.
Excluding the highest salary
SELECT MAX(SALARY) FROM employees
WHERE SALARY < (SELECT MAX(SALARY) FROM employees);
Output:
| MAX(Salary) |
| 60000 |
Using Correlated SubQuery
SELECT salary FROM employees e
WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees p WHERE e.salary<=p.salary);
Output:
| Salary |
| 60000 |
Using LIMIT Clause
SELECT salary FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2)
AS emp ORDER BY salary LIMIT 1;
Output:
| Salary |
| 60000 |
Solution for ERROR 1140
Depending on the settings and SQL version, we might sometimes receive the following error while running a query to find the employee with the maximum salary.
mysql> SELECT name, MAX(salary) AS salary FROM employees;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains a nonaggregated column,
‘database.employees.name’; this is incompatible with sql_mode=only_full_group_by
The following command can be used to help with this:
// EMPLOYEE WITH HIGHEST SALARY
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1;
// EMPLOYEE WITH SECOND HIGHEST SALARY
SELECT name, salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) ORDER BY salary DESC LIMIT 1;
// EMPLOYEE WITH Nth HIGHEST SALARY
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT (N-1), 1;
Conclusion
Finding the second highest salary in SQL in a database table can be immensely beneficial to organizations and businesses. Furthermore, these techniques are relatively simple to learn and understand once someone is familiar with basic SQL syntax. So, if you are looking to start learning SQL, this guide on how to learn SQL lists all the steps you can follow.
