SQL Leetcode: 176. Second Highest Salary

Pallavi Mirajkar Dantkale
1 min readMar 27, 2021

--

176. Second Highest Salary

Easy

1083535Add to ListShare

SQL Schema

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
  1. Using Window Rank function

SELECT
MAX(CASE
WHEN SALARY IS NOT NULL THEN SALARY
ELSE null
END ) AS SecondHighestSalary
FROM (

SELECT
Salary,
dense_rank() OVER (ORDER BY SALARY DESC) as rank_salary
FROM
Employee
) rank_table
WHERE rank_salary = 2

2. Using correlated subquery ( Top-Down Approach)

SELECT
Salary as SecondHighestSalary
FROM EMPLOYEE e1
WHERE 2–1 = (SELECT COUNT(DISTINCT SALARY) FROM Employee e2
WHERE e2.salary > e1.salary )
UNION
(SELECT null)
LIMIT 1;

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

--

--

Pallavi Mirajkar Dantkale
Pallavi Mirajkar Dantkale

Written by Pallavi Mirajkar Dantkale

QA Engineer / Data Analyst — Highly committed to Quality Assurance and data analysis, advocate for quality and add the right value to the organization.

No responses yet

Write a response