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^nd highest salary from the Employee table. If there is no 2^nd highest salary, return null.

Example

Here's the employee table:

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.

1. Eliminate Duplicate Salaries:

   • Use the DISTINCT keyword to ensure each salary is unique, preventing multiple entries of the same salary from affecting the ranking.

2. Order Salaries in Descending Order:

   • Sort the salaries from highest to lowest using the ORDER BY clause with DESC (descending) order.

3. Retrieve the Second Entry:

   • Utilize the LIMIT and OFFSET clauses to skip the highest salary and retrieve the next one, which represents the second highest salary.

4. 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.

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:

1. 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.

2. 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.

3. 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.