SQL Interview Question - How to find nth highest salary?

No video

SQL Interview Question - How to find nth highest salary?

Рет қаралды 95,062

Learn at Knowstar

Күн бұрын

Пікірлер: 75

@LearnatKnowstar 2 жыл бұрын

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

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

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

@LearnatKnowstar 2 жыл бұрын

Thank you

@Sekhrian 2 жыл бұрын

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

@komalchoudhary9896 11 ай бұрын

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

@HrHaakon 2 жыл бұрын

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

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

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

@appalabathulamadhubabu4778 2 жыл бұрын

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.

@ibrahimlita66 2 жыл бұрын

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

@LearnatKnowstar 2 жыл бұрын

Thank you for your support 👍

@Java_With_Me Жыл бұрын

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

@mmusciano 3 жыл бұрын

You are a great teacher! Thank you very much

@LearnatKnowstar 3 жыл бұрын

Thanks so much!

@shashidharr6595 Жыл бұрын

Thanks for sharing 👍

@LearnatKnowstar Жыл бұрын

Thank you

@prasangsinghal7887 2 жыл бұрын

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

Should be working

@michaldobsovic2122 2 жыл бұрын

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.

@irfan78661 Жыл бұрын

VERY INFORMATIVE AND UNDERSTANDABLE.

@LearnatKnowstar Жыл бұрын

Thank you

@shubhamkhurana7545 4 жыл бұрын

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

Yes, sure. This will work as well.

@surendrag1901 2 жыл бұрын

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

Thank you

@LearnatKnowstar 2 жыл бұрын

You might find the below playlist useful kzfaq.info/sun/PL2-GO-f-XvjBl5fpzdfYaPW28PwsLzLc4

@LearnatKnowstar 2 жыл бұрын

This video explains the rank, row number and dense rank functions kzfaq.info/get/bejne/paiie8ST3qqnhJ8.html

@surendrag1901 2 жыл бұрын

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

@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 - kzfaq.info/get/bejne/e8Vmm8R2xJeUlXk.html

@tradingstation9080 Жыл бұрын

@@LearnatKnowstar Thanks 🙂

@allanatal 2 жыл бұрын

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

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

@kristyowens2284 4 жыл бұрын

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

@LearnatKnowstar 4 жыл бұрын

Thank you

@drkenny7928 2 жыл бұрын

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

@sathyaprakash6633 3 жыл бұрын

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

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

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

@sathyaprakash6633 3 жыл бұрын

Thank you!

@kalpeshshindet_3317 2 жыл бұрын

Ima download it thanks for sharing!!

@UsmanBinIhsan 2 жыл бұрын

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

@tiagosilva856 2 жыл бұрын

You are a bless in my life 😘 Rank=2

@shashidharr6595 Жыл бұрын

Thanks u for sharing 😘

@LearnatKnowstar Жыл бұрын

Thank you

@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

@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

@gouthamganesh2324 2 жыл бұрын

Thank u sur

@CaribouDataScience 2 жыл бұрын

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.

@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

@p26p2006 2 жыл бұрын

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

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

@dongnguyen7497 2 жыл бұрын

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

@thiclordjord1109 2 жыл бұрын

That’s exactly what I was thinking.

@LearnatKnowstar 2 жыл бұрын

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

@alexanderkorn2009 2 жыл бұрын

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

@deveshpadgelwar8895 Жыл бұрын

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

@LearnatKnowstar 4 жыл бұрын

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

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

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. :)

@SAIKIRAN-uj1bz 2 жыл бұрын

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

@LearnatKnowstar 2 жыл бұрын

Please try changing the quality to HD.

@Java_With_Me Жыл бұрын

Rownum is best to find nth sal

@davidlean8674 2 жыл бұрын

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

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 !

@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 - kzfaq.info/get/bejne/e8Vmm8R2xJeUlXk.html