SQL Interview Question - How to find nth highest salary?

This video is a part of the series on scenario based SQL Query Interview Questions.
This videos discusses the most commonly asked interview question -
How to find the employee with the nth highest salary?
How to install SQL Server for practice?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.net/qnXYk5
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.net/mgVYre
Python for Everybody Specialization
imp.i384100.net/DVz7Aj
IBM Data Science Professional Certificate
imp.i384100.net/LPQvg3
Google Data Analytics Professional Certificate
imp.i384100.net/OR37oQ
Coursera Plus - Data Science Career Skills
imp.i384100.net/c/3299742/132...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.com/company/know...
Email - [e-mail]

[@LearnatKnowstar]

Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!
imp.i384100.net/Ke51on

[@javiersanchez4549]

i would use row_number over (partition by EmployeeIr order by Salary desc) as rowc. And then use it as a subquery and use "where rowc = nth position"

[@raghavenderreddy8193]

You are explaining in precise manner that every one can understand who does not even know about SQL.

[@LearnatKnowstar]

Thank you

[@komalchoudhary9896]

in this era, efficient coding matters and we could directly get nth salary by rank/ rownumber(), but told us all the jugad we can do to get nth salary...... amazing....... thanks.....

[@Sekhrian]

can use row_number over(order by salary desc) as rnk , put it in nested select and then select where rnk = 9

[@kalpeshshindet_3317]

Ima download it thanks for sharing!!

[@HrHaakon]

I mean, in Oracle, you'd just do something like this:
SELECT *
FROM EMPLOYEE
ORDER BY salary desc
OFFSET (n-1) ROWS FETCH FIRST 1 ROW ONLY
;
And MS SQL Server has an offset or skip or something too. I'm sure of it.

[@andresilvasophisma]

Offset is not a good solution as it has to process all of the offset records so that it can get to the one that you want.

[@HrHaakon]

@@andresilvasophisma
And? It's a one off query.
You could, I guess select, order by salary fetch first n only, and select from that again, but order asc fetch first 1 row only.
But then we're assuming that column is indexed, and that is not a safe assumption either...

[@mmusciano]

You are a great teacher! Thank you very much

[@LearnatKnowstar]

Thanks so much!

[@ibrahimlita66]

thanks a lot .. I downloaded all series to learn me all these beautiful triks and samples ..thanks again

[@LearnatKnowstar]

Thank you for your support 👍

[@gouthamganesh2324]

Thank u sur

[@kristyowens2284]

I have been asked this a lot of times. Thanks for posting relevant content.

[@LearnatKnowstar]

Thank you

[@drkenny7928]

The rank function does a better job than the multiple nested querries

[@irfan78661]

VERY INFORMATIVE AND UNDERSTANDABLE.

[@LearnatKnowstar]

Thank you

[@shashidharr6595]

Thanks for sharing 👍

[@LearnatKnowstar]

Thank you

[@UsmanBinIhsan]

Could you please share the link for the Demo Database you are using in this video.Thanks

[@tiagosilva856]

You are a bless in my life 😘
Rank=2

[@allanatal]

Very good content. As a feedback, if you could zoom in the screen to show the code it would be better to watch your videos on smartphones

[@LearnatKnowstar]

Thank you. In more recent videos, you can find bigger font size👍

[@shashidharr6595]

Thanks u for sharing 😘

[@LearnatKnowstar]

Thank you

[@surendrag1901]

Thanks for sharing the video's with us. I like the way which you have explained. These are the basic question which we face in interviews. Could you please share the video link if you have explained about analytical functions ?

[@LearnatKnowstar]

Thank you

[@LearnatKnowstar]

You might find the below playlist useful
th-cam.com/play/PL2-GO-f-XvjBl5fpzdfYaPW28PwsLzLc4.html

[@LearnatKnowstar]

This video explains the rank, row number and dense rank functions
th-cam.com/video/oBrHabyDCTk/w-d-xo.html

[@surendrag1901]

@@LearnatKnowstar Thanks a lot. This would be helpful for me

[@sathyaprakash6633]

Thanks for posting relevant content.
Can you please help me with below scenario
How do we group or list first 10 rows combine into a another single row and then another 10 rows combine into a another row, this should go on till the end?

[@LearnatKnowstar]

You can use Windows function as mentioned in the post below.
stackoverflow.com/questions/41656620/sql-query-for-calculating-average-in-every-n-rows-with-step-1

[@LearnatKnowstar]

We will do a detailed video soon. Thank you for sharing your query.

[@sathyaprakash6633]

Thank you!

[@prasangsinghal7887]

Hi ma'am. Thanks for this tutorial.
I am using postgres so can I use OFFSET and LIMIT together to fetch Nth record?
For example, to fetch the 3rd highest salary, can we do something like this?
SELECT salary
FROM employee
ORDER BY salary DESC
OFFSET 2 LIMIT 1;

[@martinhansen3673]

Should be working

[@michaldobsovic2122]

Yes, but not in case if first two or three highest salary are the same. Dense_rank function does slightly different thing than offset/limit. Dense rank will return all emps with 3rd highest salary regardless of count of employees with 1st and 2nd highest salary.

[@tradingstation9080]

explanation is awesom if you provide dataset then it will more helpfull .because it will take much to create again and again different table.

[@LearnatKnowstar]

Thank You. We have started including the datasets in our recent video tutorials. A similar dataset as needed for this example is provided in the tutorial on SQL Complex Queries which can be accessed here -
th-cam.com/video/E_6haE_10eE/w-d-xo.html

[@tradingstation9080]

@@LearnatKnowstar Thanks 🙂

[@shubhamkhurana7545]

Well, can't we just use LIMIT keyword
Select * from (Select * from Table_Name order by salary desc limit n) as v order by salary limit 1

[@LearnatKnowstar]

Yes, sure. This will work as well.

[@appalabathulamadhubabu4778]

Madam, if there are duplicate Salaries then top will not work.
For example :
If there are salaries like 5000, 4000, 3000, 3000 etc. and we want top 3rd highest salary.
If we use top 3 means it will take 5000, 4000, 3000, it wont take another 3000

[@edgarmachado6008]

If we use the Order by clause, then we use could Top 3 with Ties. This will return all rows whose values tie with the record in the 3rd position.
We could always create a list of distinct(unique) salaries first and then return the top x salaries and wouldn't have to worry about duplicates.

[@Java_With_Me]

Select * from emp e1 where n=(select count(distinct e2.rownu.)
From emp e2 where e1.sal

[@RK-wf7re]

Excellent video. In the first option of nth max salary i f we want 3rd max salary do we need to write subqueries 3 times?

[@tarungangadhar14]

have you got solution using max function if so mention the code

[@RK-wf7re]

@@tarungangadhar14 i didn't try yet

[@CaribouDataScience]

Thanks, isn't there something like a" nth_ " function?

[@edgarmachado6008]

In T-SQL, the dialect used by Microsoft, there is no Nth function. The easiest way to find the Nth highest or lowest value in dataset is by using the Row_Number() function. It's a Window function and makes use of the Over clause. Actually, the Rank() and Dense_Rank() functions could as be used as well but Row_Number() is a fine choice. The only real difference between the three functions has to do with tied row values. Row_Number() ignores tied row values. The other two do not. If there are no tied row values in the dataset , then all three functions behave exactly the same.
Once you work out your logic you could create either a user defined scalar function, or a user define table valued function to make it easy to invoke when needed.

[@abidanjumansari9769]

mam if you will give create and insert script, it would be very helpful

[@nagendrababunagineni1609]

what about if you have max salary having more then one employee by using top clause it shows only one employee details

[@p26p2006]

If there is a tie in salary how will we get the nth highest as values will be duplicated.how to solve this issue.

[@LearnatKnowstar]

You need to decide between using rank, dense rank and row number. They number the rows in a slightly different way.
You can learn more about the functions here -
docs.microsoft.com/en-us/answers/questions/211223/what-is-the-difference-among-row-number-rank-and-d.html

[@LearnatKnowstar]

Have you been asked a SQL query interview question that you couldn't answer?
Let us know in the comments below and we will answer those in our upcoming videos!

[@faizanyounastanooli3598]

In your second way if you have 3 employees with same salary then we will not be able to get nth highest salary. Lets say we have 3 employees with salary 85000 and 4th is 70000 then it means our 2nd highest salary is 70000 not 85000.

[@HrHaakon]

Make your inner query "SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC" and you'll get unique salaries.
But both would be correct, so you should ask your interviewer to clarify what he means. :)

[@deveshpadgelwar8895]

How to find 3rd. High salary by using CTE ?

[@dongnguyen7497]

Dude, why not just use "order by salary" then "offset" the number of row you want to skip then "fetch " the first row

[@thiclordjord1109]

That’s exactly what I was thinking.

[@LearnatKnowstar]

You can certainly do the offset method as well. Thanks for mentioning it.

[@alexanderkorn2009]

@@LearnatKnowstar the power of sql is in its expressiveness. The solution provided by a pal from upstairs is much clearer and is more understood. By the way the solution uses only sql syntax that can be ported into any dbms scripts without almost any change

[@Java_With_Me]

Rownum is best to find nth sal

[@SAIKIRAN-uj1bz]

Presentation is not clear, if picture is clear this will be very helpful

[@LearnatKnowstar]

Please try changing the quality to HD.

[@chinna7485]

SELECT E_NAME, E_SAL, ROW_NUMBER() OVER ( ORDER BY E_SAL DESC) AS POSITION FROM EMPS ORDER BY E_SAL DESC
THIS WAY ALSO WE CAN

[@vikassingh-ql7ef]

Where is the database file for this

[@LearnatKnowstar]

Unfortunately, the database for this tutorial is not available but a similar database is available for the tutorial on SQL Complex queries which can be accessed here -
th-cam.com/video/E_6haE_10eE/w-d-xo.html

[@davidlean8674]

Why is this written as an "interview question". It is merely a tip that helps people improve their skills. The obsessive focus of predominantly Indian nationals on "Passing exams" & "passing interviews" can disadvantage their fellow countryman. They get perceived as just capable of scraping thru an interview. Only to find they are useless when actually doing any task that requires them to assimilate a variety of techniques & coding patterns to solve real business problems.
As this has nothing to do with interviews. Why phrase it that way?

[@LearnatKnowstar]

Thank you for sharing your thoughts. You are right in saying that greater learning is needed and passing an interview should not be an end in itself. This should be true for everyone and not any one particular country. We have all encountered brilliant coders from all countries including India. Cheers for that !

[@piyushgarg1400]

hi can u please tell where is the question code and solution code of query u used in the video it will be great help