LeetCode 177: Nth Highest Salary [SQL]

Solution and walkthrough of leetcode database problem 177: Nth 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/problems/nth-hig...
Check out my StrataScratch videos: • StrataScratch Coding Q...

[@frederikmuller]

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

[@mindyng1124]

clear, thorough and quick to digest especially at 1.25 speed :D keep up the very helpful and excellent content! thx Frederik

[@hsrboys9993]

Love your videos - much love from Berkeley!

[@cathymccarthy4585]

Straight to the point, considers edge cases. Thanks for the helpful video!

[@mohammadghazanfar7228]

What about the NULL part? What if the Nth place has no salary, NULL - that wasn't taken in cosnideration?

[@mrprime557]

Love the way you explain. I have been making the same mistakes. Instead of going to the answer directly, youve mentioned your mistakes and guided to answer :)

[@frederikmuller]

sometimes you just gotta be honest and not let pride get in the way :)

[@lakshmim1759]

MS SQLServer :
select nullif(salary,null) from
(select DISTINCT Salary
from (select Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) as d_rank from Employee) as sub where d_rank = @N
)as salary

[@sundaytalks1390]

Thank you sir!

[@mickyman753]

do we need to learn these function things too ?

[@viru_viral]

When I try to run the code with the solution you described in the video, It fails with "Runtime Error" message. Submitting the same code accepted though, did not find a clear explanation in leetcode discussion as well. Can you let me know why that might be happening?

[@user-zf3it3vw2e]

very helpful

[@vaibhavpatharkar6794]

Very nice bro. But is it mysql or postgre becz i never studied SET operation and Function in sql. Pl help here. Pl give explanation for this

[@anushreepatil4811]

Question :
List the riders_id who started their journey yesterday in Delhi and completed their last journey in Noida that too yesterday.
Columns were:
riders_id, drivers_id, city_from,city_to,pickup_time(timestamp),drop_off_time(timestamp)
I was asked this question in one of my uber interviews. Could you please solve it for me?

[@frederikmuller]

I think there are some words missing from the question.

[@stockjanitor]

hello, i been watching your video, it is helpful and clear, but when submit to leetcode, the codes wont work,

[@frederikmuller]

maybe you need to change the SQL dialect. I’m using MySQL.

[@mritunzaysingh8978]

can anyone tell me that my query in a right way or not
select salary
FROM
(
select id,salary,rank() over(order by salary desc) as rnk
from person
) A
where A.rnk = 2;

[@mritunzaysingh8978]

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

[@yajurnigam2567]

SQL SERVER
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN (
Select distinct salary from (Select salary ,Dense_Rank() over(order by salary desc) DENSERANK from Employee) as q where DENSERANK=@N

);
END

[@the_fat_journal]

This is good but won't work in SQL Server, we don't have anything like offset there.
this is how I wrote it for SQL Server:
"select distinct e1.salary from employee e1 where (@N-1) = (select count(distinct salary) from employee e2 where e2.salary > e1.salary)"

[@frederikmuller]

Thank you for contributing your solution!

[@hamzaehsankhan]

Could you please explain your solution as well?

[@reddytintaya6749]

I was crazy, why this was not working ... I forgot the ; :| 😆