LeetCode 176: Second Highest Salary [SQL]

Solution and walkthrough of leetcode database problem 176: Second Highest Salary. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.

[Kaviarasu_NS]

Watching Frederik explain feels like a meditative session ❤

[haoqingcai]

Very good explanation. Really solved all my confusion about this problem. Keep going like. Really enjoy content like this!

[khoanguyen]

I am really appreciate your work. Are you going to make all SQL problems on Leetcode (153 questions) ? Thank you!

[vaibhavpatharkar]

What an Amazing explanation. Pl take more problems from leetcode, scratascatch and hackerrank. You explain perfectly. May Shree Raam bless you with joy and happiness..❤

[namanverma]

while selecting " where salary not in select MAX(Salary) from employee, why it does not includes 3rd also as we did not set any limit

[truptipradhan]

Thanks for explaining the solution really well. Just thought of another solution with rank function.

SELECT
case when b.salary is null then null else b.salary end as salary
FROM
(select 2 as rnk) as a
LEFT OUTER JOIN
(select salary, rnk from
(select
salary,
rank() over(order by salary desc) as rnk
from
dbo.Employee
) a
where rnk = 2
) as b
on a.rnk = b.rnk

Got bit messy as the requirement was if there is no second highest salary, return NULL.

[udayptp]

I think the second approach did not work because salary column can contain duplicate salaries so when you write offset 1 it will skip 1 row but because of duplicate entries your limit 1 will give you again same salary.. I think you are getting me.??

[vichitravirdwivedi]

Wonderful explaination. As a recommendation,
Please zoom in. It is difficult to see the letters clearly

[CoumbaSACKO]

Hello Frederik,
Thank you so much for your content; very helpful.
I have a question for this video, why did you use the WHERE Clause with an aggregation 'AVG.'? I am a bit confused here.

[balajivenkatesh]

-- Write your PostgreSQL query statement below

select max(salary) as SecondHighestSalary
from Employee
where salary < (select max(salary) from Employee)

[DarkKnight-xlrj]

Whats gonna happen to the 100? as it is also less than the max salary 300?

[alkamaahmed]

Are you not making anymore videos on SQL ?

[prnvsgr]

now this is a medium level question ...tricky but fun

[khoanguyen]

Hello Frederik, Are you still making premium leetcode SQL problems? Thank you

[mritunzaysingh]

Second highest salary

create table Person
(id int not null primary key,
Salary INt
);

insert into person VALUES(1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000);

select * from person;

select salary
FROM
(
select id, salary, rank() over(order by salary desc) as rnk
from person
) A
where A.rnk = 2;

This is a right approach or not please any one can tell me

[tpsjiajeud]

SELECT MAX Salary as SecondHighestSalary
FROM employes
WHERE salary NOT IN(SELECT MAX Salary) FROM employees

[muhammadsalar]

Wasn't aware Fardeen khan taught SQL in his younger days..