0% completed
Problem
Table: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.
Problem Definition
Write a solution to find the 2<sup>nd</sup> highest salary from the Employee
table. If there is no 2<sup>nd</sup> highest salary, return null
.
Example
Here's the employee table:
Expected Output
Try It Yourself
Solution
To determine the second highest salary from the Employee
table, we analyze the provided SQL query, which effectively retrieves the desired result by leveraging SQL's DISTINCT
, ORDER BY
, LIMIT
, and OFFSET
clauses. Below is a comprehensive breakdown of the approach, step-by-step execution, and detailed explanations for each component of the SQL query.
-
Eliminate Duplicate Salaries:
- Use the
DISTINCT
keyword to ensure each salary is unique, preventing multiple entries of the same salary from affecting the ranking.
- Use the
-
Order Salaries in Descending Order:
- Sort the salaries from highest to lowest using the
ORDER BY
clause withDESC
(descending) order.
- Sort the salaries from highest to lowest using the
-
Retrieve the Second Entry:
- Utilize the
LIMIT
andOFFSET
clauses to skip the highest salary and retrieve the next one, which represents the second highest salary.
- Utilize the
-
Handle Cases with Fewer Than Two Salaries:
- If there is no second distinct salary (i.e., all employees have the same salary or there's only one employee), the query will return
null
.
- If there is no second distinct salary (i.e., all employees have the same salary or there's only one employee), the query will return
SQL Query
SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1;
Step-by-Step Explanation
Step 1: Select Distinct Salaries
SQL Snippet:
SELECT DISTINCT salary
Explanation:
SELECT DISTINCT salary
: To create a list of unique salary values, ensuring that duplicate salaries do not skew the ranking.
Step 2: Order Salaries in Descending Order
SQL Snippet:
ORDER BY salary DESC
Explanation:
ORDER BY salary DESC
: To arrange the salaries from highest to lowest, facilitating the identification of the second highest salary by position.
Step 3: Retrieve the Second Highest Salary
SQL Snippet:
LIMIT 1 OFFSET 1;
Explanation:
LIMIT 1
:- Restricts the number of rows returned to 1.
OFFSET 1
:- Skips the first 1 row of the result set.
Combined Effect:
LIMIT 1 OFFSET 1
:OFFSET 1
: Skips the first row (which, after sorting, is the highest salary).LIMIT 1
: Retrieves the next row, which is the second highest salary.
Alternative Approaches
While the provided SQL query effectively retrieves the second highest salary, there are alternative methods to achieve the same result. Below are a few common approaches:
-
Using Subqueries with
MAX
:SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);
Explanation:
- The inner subquery
(SELECT MAX(salary) FROM Employee)
retrieves the highest salary. - The outer query selects the maximum salary that is less than the highest salary, effectively the second highest salary.
- The inner subquery
-
Using
ROW_NUMBER()
:SELECT salary AS SecondHighestSalary FROM ( SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM ( SELECT DISTINCT salary FROM Employee ) AS distinct_salaries ) AS ranked_salaries WHERE rn = 2;
Explanation:
- Inner Subquery (
distinct_salaries
): Selects distinct salaries. - Middle Subquery (
ranked_salaries
): Assigns a row number to each distinct salary in descending order. - Outer Query: Retrieves the salary where the row number is 2, i.e., the second highest salary.
- Inner Subquery (
-
Using
DENSE_RANK()
:SELECT salary AS SecondHighestSalary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM Employee ) AS ranked_salaries WHERE dr = 2;
Explanation:
DENSE_RANK() OVER (ORDER BY salary DESC)
assigns a rank to each salary, with no gaps in ranking.- The outer query selects the salary where the dense rank is 2, representing the second highest salary.
Note: The choice of method can depend on specific database capabilities and performance considerations.
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible