LeetCode 176: Second Highest Salary [SQL]

No video

LeetCode 176: Second Highest Salary [SQL]

Рет қаралды 19,729

Күн бұрын

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.
Link to the problem: leetcode.com/p...
Check out my StrataScratch videos: • StrataScratch Coding Q...

Пікірлер: 38

@frederikmuller 2 жыл бұрын

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

@Kaviarasu_NS 6 ай бұрын

Watching Frederik explain feels like a meditative session ❤

@haoqingcai4008 3 жыл бұрын

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

@truptipradhan86 2 жыл бұрын

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

@vinoddiwan5792 3 жыл бұрын

5: 14 updated Use DISTINCT Keyword

@shaamidrees6036 10 ай бұрын

Great explanation

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

@khoanguyen1194 3 жыл бұрын

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

@frederikmuller 3 жыл бұрын

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

@khoanguyen1194 3 жыл бұрын

@@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 3 жыл бұрын

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

@udayptp 3 жыл бұрын

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 2 жыл бұрын

Try "Select distinct"

@tpsjiajeud4784 10 ай бұрын

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

@namanverma626 8 ай бұрын

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

@vichitravirdwivedi 2 жыл бұрын

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

@frederikmuller 2 жыл бұрын

Thank you for the input!

@mritunzaysingh8978 2 жыл бұрын

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

@luckychitundu1070 2 жыл бұрын

Nice explanation my brother

@vishalplayzz2580 Жыл бұрын

man these are too good 🙌

@ceyhunozturk5115 Жыл бұрын

Thanks

@prnvsgr Жыл бұрын

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

@CoumbaSACKO 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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

@mrprime557 2 жыл бұрын

AWESOME !

@ziqi6808 3 жыл бұрын

very nice thanks!"

@DarkKnight-xl5rj 2 жыл бұрын

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

@muhammadsalar607 Жыл бұрын

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

@khoanguyen1194 2 жыл бұрын

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

@frederikmuller 2 жыл бұрын

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!