How to Get the Second Highest Salary from a MySQL Database

Second Highest Salary from a MySQL Database

In database management, retrieving specific information from tables is a common task. A frequently asked question is how to get the second highest salary from the employee salary table in MySQL. In this blog post, we will explore various SQL queries and techniques to accomplish this task efficiently.

Scenario: Employee Salary Table

For the purpose of this demonstration, let's say we have a table called "Employees" with the following schema:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL

);

We want to find the second highest salary from this table.

SQL Query Methods

Method 1: Using the LIMIT Clause

Second, a common approach to finding the highest salary is to use a limit clause in combination with an order by clause. Here's the SQL query:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC

LIMIT 1 OFFSET 1;

This query selects distinct salary values, orders them in descending order, and then uses Limit 1 offset 1 to skip the first highest salary and retrieve the second highest salary.

Method 2: Using a Subquery

Another method involves using a subquery to find the second highest salary. Here's the SQL query for this approach:

SELECT MAX(salary) AS second_highest_salary
FROM employees

WHERE salary NOT IN (SELECT MAX(salary) FROM employees);

In this query, the subquery selects the highest salary from the "Employee" table, and then the main query finds the maximum salary that is not equal to the highest salary. This effectively gives us the second highest salary.

Method 3: Using a Self-Join

You can also use self-join to find the second highest salary. Here's the SQL query for this approach:

SELECT MAX(e1.salary) AS second_highest_salary
FROM employees e1

WHERE e1.salary < (SELECT MAX(e2.salary) FROM employees e2);

In this query, we join the "Employees" table using the aliases E1 and E2. We then compare E1's salary with the maximum salary found in E2 to identify the second highest salary.

Retrieving the second highest salary from a table in MySQL can be accomplished using various SQL query methods. Depending on your specific use case and preferences, you can choose one of the methods outlined in this blog post. Whether you prefer the simplicity of limit clauses, the elegance of subqueries, or the flexibility of self-joining, MySQL offers a number of solutions to meet your needs.