LeetCode 176: Second Highest Salary [SQL]

[@frederikmuller]

If you want to have access to more free SQL problems, check out StrataScratch: stratascratch.com/?via=frederik

[@Kaviarasu_S]

Watching Frederik explain feels like a meditative session ❤

[@haoqingcai4008]

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

[@vinoddiwan5792]

5: 14 updated
Use DISTINCT Keyword

[@khoanguyen1194]

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

[@frederikmuller]

yes, I'm planning to go through all of them!

[@khoanguyen1194]

@@frederikmuller Thanks sir, its so great I tried to study a lot sources I feel I understand them but when I actually work on real problem I feel I dont know anything and really appreciate your explanation for every single problems.

[@frederikmuller]

@@khoanguyen1194 I'm glad it's helpful! I think the most important thing is to learn how to approach a problem so you can apply your knowledge to any problem.

[@truptipradhan86]

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.

[@ft_peakhd2921]

select ifnull((select distinct salary from table_name ordre by salary desc limit 1 offset 1),null) as second_highest_salary
This will give you NULL as return incase if there's no second highest. simple but effective one

[@namanverma626]

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

[@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.??

[@dazai9015]

Try "Select distinct"

[@balaji27venkatesh]

-- Write your PostgreSQL query statement below
select max(salary) as SecondHighestSalary
from Employee
where salary < (select max(salary) from Employee)

[@vichitravirdwivedi]

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

[@frederikmuller]

Thank you for the input!

[@luckychitundu1070]

Nice explanation my brother

[@vaibhavpatharkar6794]

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

[@prnvsgr]

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

[@DarkKnight-xl5rj]

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

[@VsEdits59]

man these are too good 🙌

[@shaamidrees6036]

Great explanation

[@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.

[@frederikmuller]

Do you mean MAX()? Since the aggregation is in a separate subquery that will be evaluated first, it’s fine. But you’re right, you’re not able to use aggregation functions in a where clause otherwise.

[@CoumbaSACKO]

@@frederikmuller Thank you so much; I appreciate your quick feedback.

[@alkamaahmed2872]

Are you not making anymore videos on SQL ?

[@frederikmuller]

I'll start with the premium SQL questions soon ;)

[@alkamaahmed2872]

@@frederikmuller That's great !!

[@khoanguyen1194]

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

[@frederikmuller]

Not at the moment. I feel like I’ve covered all major question types on LeetCode and I have a sponsorship with StrataScratch which is why I’m prioritizing SS. Also more questions on there!

[@mritunzaysingh8978]

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

[@joeljx007]

Hey I did this but it failed for me in Case 2, because it doesn't print NULL in case there is no data in the table

[@tpsjiajeud4784]

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

[@muhammadsalar607]

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

[@mrprime557]

AWESOME !

[@tttttt6808-l7u]

very nice thanks!"

[@stellaueda4565]

Tricky indeed

[@АлексейСоловьев-о9д]

Thanks

[@ceyhunozturk5115]

Thanks