HomeОбразованиеRelated VideosMore From: kudvenkat

Part 1 How to find nth highest salary in sql

3432 ratings | 859417 views
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-1-how-to-find-nth-highest-salary_17.html This is a very common SQL Server Interview Question. There are several ways of finding the nth highest salary. By the end of this video, we will be able to answer all the following questions as well. How to find nth highest salary in SQL Server using a Sub-Query How to find nth highest salary in SQL Server using a CTE How to find the 2nd, 3rd or 15th highest salary Let's use the following Employees table for this demo Use the following script to create Employees table Create table Employees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50), Salary int ) GO Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Mark', 'Hastings', 'Male', 60000) Insert into Employees values ('Steve', 'Pound', 'Male', 45000) Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000) Insert into Employees values ('Philip', 'Hastings', 'Male', 45000) Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000) Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000) Insert into Employees values ('John', 'Stanmore', 'Male', 80000) GO To find the highest salary it is straight forward. We can simply use the Max() function as shown below. Select Max(Salary) from Employees To get the second highest salary use a sub query along with Max() function as shown below. Select Max(Salary) from Employees where Salary [ (Select Max(Salary) from Employees) To find nth highest salary using Sub-Query SELECT TOP 1 SALARY FROM ( SELECT DISTINCT TOP N SALARY FROM EMPLOYEES ORDER BY SALARY DESC ) RESULT ORDER BY SALARY To find nth highest salary using CTE WITH RESULT AS ( SELECT SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK FROM EMPLOYEES ) SELECT TOP 1 SALARY FROM RESULT WHERE DENSERANK = N To find 2nd highest salary we can use any of the above queries. Simple replace N with 2. Similarly, to find 3rd highest salary, simple replace N with 3. Please Note: On many of the websites, you may have seen that, the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates. WITH RESULT AS ( SELECT SALARY, ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER FROM EMPLOYEES ) SELECT SALARY FROM RESULT WHERE ROWNUMBER = 3
Html code for embedding videos on your blog
Text Comments (370)
Abel Mengistu (2 years ago)
Thank you for the resources, they are very helpful
Tech Reviews (3 months ago)
Another way to nth highest salary is this : Select min(Salary) from (Select distinct top 7 Salary from Employee order by Salary desc) Result :: :: Like my comment if my query works
kudvenkat (2 years ago)
+Abel Mengistu Thanks a lot for stopping by to provide your feedback. This means a lot to me. I am really glad you found the video tuorials useful. All the Pragim Tech video tutorials are organised in to playlists, which help you find the videos you are looking for easily https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd You can also order DVDs or download all the videos and slides for offline viewing using the link below http://www.pragimtech.com/kudvenkat_dvd.aspx Image version of the slides and text Version of the videos are on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use our free channel. https://www.youtube.com/watch?v=y780MwhY70s To receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=kudvenkat If you like these videos, please click the THUMBS UP button below the video. We like to see these free video tutorials helping others as well. Please share the link with your friends and family who you think would also benefit from them. Thanks Venkat
Yeezy (2 days ago)
Couldn't we use rank instead of dense rank? in rank the duplicate ones get skipped
Nijamuddin sayyed (5 days ago)
Really u t great sir . Thank u so much sir. I have never seen like this video. It is very helpful for me.
Anurag S (9 days ago)
everything is good. but it is not 6 or 7 thousand... infact it is sixty thousand and seventy thousand
Jyotsana Singh (22 days ago)
Excellent..explanation was clear
Sambit Pati (23 days ago)
Sir please make video tutorial for msbi and powerBI if possible
Inderjeet Singh (26 days ago)
Hi dear, Pls can someone tell me why 'Result' word was used in first subquery example.... Bcoz without it, it does not work... Plsss
Ankita tripathi (28 days ago)
Sir ,having 3 year experience what kind of question will be ask for SQL ..
rakhshanda mujib (29 days ago)
Your voice is so soothing... :)
with result as (select *,DENSE_RANK() over(order by salary desc )as denserank from manager) select salary from result where result.denserank=2 while executing this query i am getting an error like this " Incorrect syntax near 'result'."
Narendra R (1 month ago)
Splendid explanation...
Gamal Abdall (1 month ago)
This is just a beauty, I really loved the new Dense_Rank Function, kudvenkat you are the man. Thanks a million.
MARY MARGARET (1 month ago)
Ur voice is awesome
Ankita tripathi (1 month ago)
sir , r u using 2016 sql
Ankita tripathi (1 month ago)
Thank you..it is very important qun of an interview,which i was looking for that
Nawal Tarek Matarid (1 month ago)
OMG why did I just discover your channel... I have so much to catch up on.... *Goes on to watch all your vids*
venkatesh waran (1 month ago)
Chance eila very nice thanks lot
Durga Mallesh (1 month ago)
Why Top command is not working in my system ?
Bakari Tijjani a (1 month ago)
Tnxs
how to show the first 10 employees from the employee table after the entity model generated for all the employees
Jairaj Singh Kushwaha (2 months ago)
What is the difference between a stored procedure and a view and When should I use stored procedures, and when should I use views ?
Jairaj Singh Kushwaha (2 months ago)
Hi Sir, How to swap two string type columns values in a single table without using temp or third variable in Sql Server ?
K Maduri (2 months ago)
Hi Venkat, Excellent explanation, really appreciated, Thanks for the video, your time and explanation. Can you please forward any other videos on SQL as well as Cognos if possible?, Thanks.
devanand shukla (2 months ago)
Hi Vinkat, please upload the video for alternate of Union/Union All
Uma Tripathi (2 months ago)
How to write a query for fetching a record inserted yesterday.
Amit Goel (2 months ago)
Sir, can we query the same questions without using sub query?
Akshatha a (2 months ago)
Very well explained thank you so much😊
Ram Kris (2 months ago)
Will this work with oracle, i dont think so as the TOP clause is not supported in oracle
Karan Dang (2 months ago)
Can you please explain why did you put a GO command there or is there any video where GO is explained in little detail.
Jeelu Hyd (2 months ago)
It is very clear.thanks lot
Shakir Ullah Khan (2 months ago)
What is different between vachar and nvachar?
Taffessech Tessema (2 months ago)
Thanks a lot cleared my confusion
monica vyas (2 months ago)
very nice, could you please put more related questions
Veerendrakumar Prajapati (2 months ago)
I faced more complex like join department table and find department wise nth number of salary
Anurag Kushwaha (2 months ago)
TOP clause is not working in oracle 12c ????? can you please tell me why but you used TOP clause
poorna pragna (2 months ago)
Thank you very much sir, Pls let us know 'how to find duplicate row in a table' ?
poorna pragna (3 months ago)
-- Return the 2nd highest salary in the employee table- (even we can use NOT IN) Select Max(salary) From employee Where salary NOT IN (select Max(salary) from employee) Correct me if am wrong !
Ganesh Mhaske (6 days ago)
Yes, it's correct. Just a little correction put TOP 1 in-front of Max(salary) o.w it will return all the salaries which are less than top max one.
poorna pragna (3 months ago)
Excellent One sir thanks teaching nth highest salary using Sub-Query !!
jayavardhan naidu (3 months ago)
how can we add column in a table in stored procedure at the time of execution in sql server
sai venkat prakash (3 months ago)
Tq very much for your good explanation
Tech Reviews (3 months ago)
Another way to nth highest salary is this : Select min(Salary) from (Select distinct top 7 Salary from Employee order by Salary desc) Result :: :: Like my comment if my query works
Mayank Sharma (3 months ago)
Thank you very much. After going through your tutorials now I am able to solve the questions which were asked to me in an interview. However, I am also trying to display the name of the employee along with the Nth highest salary but it is showing errors. Can you please tell me how to include the name as well. I tried simply adding the name but that didn't work.
Ankush Singh (3 months ago)
can you please share all .Net and Sql interview Series links.....
Harsh Vardhan Dewari (3 months ago)
can we use OFFSET and FETCH to get nth highest?
clashofclan ram (3 months ago)
Select e.sal from employee e where n-1 = (select count (e1.sal) from employee e1 where e1.sal>e.sal ) it will work in every SQL database
Thank you very much sir!!
Paul D (4 months ago)
I give this video a Dense Rank value of 1. Very nice! Only suggestion is to use ZOOM, as it's difficult to view on tablet or similar device.
anjali goorha (4 months ago)
Top keyword is not working..error showing
zulqadar idrishi (4 months ago)
Dear sir, I dont know where are you from and who are you but i know one thing that You are realy awesome. I realy thank to you for your support by your video.
sumanth komaragiri (4 months ago)
Sir i am a RDBMS beginner and i am practising it in Basic (cmd/Terminal) so my doubt is that How to find out The Name of the employee who is getting MAX(SALARY) ?? can anyone helpme
sumanth komaragiri (4 months ago)
B sai mohan goud Thank you so much bro ! it works. i tried searching it on net but i dint find ..
B sai mohan goud (4 months ago)
SELECT Name, Salary FROM Employee WHERE Salary = (SELECT Max(Salary) FROM Employee)
Prasad (4 months ago)
Thank you so much sir for great help to learn technology.
Uday Kiran (4 months ago)
Awesome
HARSH (4 months ago)
Can anyone please tell me how to give the table name dynamically in SELECT query. Thanks in advance.
Mokhlesur Rahman (4 months ago)
So much helpful and learnable all of your's video ... but sound is so low to understand, please
mohsen rezvani (4 months ago)
if you use partition by in your Row_num function it'll work fine
Siddharth Khade (5 months ago)
why is the need to sort in the subquery, if the data is again sorted outside it
Mahendra Chandekar (5 months ago)
How can we get 7th high salary without using aggregate function,rownum?
Mahendra Chandekar (5 months ago)
How can we get 7th high salary without using aggregate function,rownum?
Ravi Chandra kancharlah (5 months ago)
Please let me know sql queries for below Order_id customer_Id purchaseDate movie_Id minutesStreamed O1 C1 1-Jan-00 P1 100 O2 C2 1-Jan-02 P2 90 O3 C3 1-Apr-02 P3 93 O4 C4 1-Apr-03 P1 99 O5 C4 1-Jan-06 P2 99 O6 C1 1-May-06 P5 89 O7 C4 1-Dec-17 P5 89 O8 C3 3-Mar-18 P1 145 1) Write a SQL statement which returns the customer_Ids that have purchased both movie_Ids P1 & P5. 2) Write a SQL statement which returns the movie_Id with the most minutes Streamed. 3) Write a SQL statement that returns the customers who purchased one and only one movie_Id. 4) Write a SQL statement which can generate the list of customers whose minutes Streamed is consistently less than the previous mintues Streamed. As in minutes Streamed in the nth order is less than minutes Streamed in n-1 th order, and the next previous order is also less. Another way to say it, list the customers that watches less and less minutes each time they watch a movie.
pratik chhapolika (5 months ago)
My 2nd question: How to find name of all employees having 2nd highest salary?
pratik chhapolika (5 months ago)
Suppose we have Flight Data. There are 3 columns " To_City" "From_City" and Fare. Now the table contains duplicates in terms of example: Kolkata to Delhi Price:600 , there may be another row Delhi to Kolkata Price 600, which means one and same thing (Assuming Price to be same for up and down journey.) How to remove such duplicates?
Harshapriya Pokuri (5 months ago)
thank for this explaination, nice
Nikhil Jilhawar (5 months ago)
👌👌👌👌👌
Gaurav Mishra (5 months ago)
WITH RESULT AS (select amount, DENSE_RANK() over(order by amount desc) as DENSERANK) from salary (select amount from RESULT where RESULT.DENSERANK=3) showing this result guide me please, Msg 156, Level 15, State 1, Line 28 Incorrect syntax near the keyword 'from'.
Rajat Joshi (5 months ago)
You got an amazing voice...helps to learn quickly
Rajesh Gaikar (5 months ago)
Simple elegant and Great explanation..
Shanks24 A (6 months ago)
Your explanation is very precise, I love the way u first write sub query first and then the outer query..it just fits in mind quickly. One more thing others uses rownumber function , coz they first make it distinct rows and accordingly top number is given for req. Salary.
amit chaudhary (6 months ago)
select salary from(select distinct salary from user order by salary limit n)as result limit 1; is this query ok for nth highest salary???
Narottam Saini (6 months ago)
Hi Venkat, Please let me know if you have any videos for Database migration, as nowadays lot of openings are coming for the same. Videos I'm looking for like validating Views, indexs, count, primary/Foreign key, Stored procedue checks ect.. Any help much appreciated :)
Sql Server (6 months ago)
This is Best method http://shrinkearn.com/hRVR
Smana P (6 months ago)
Thank you for ur time, very useful tutoria
khadija Ait- (6 months ago)
thank youuu so mutch i always get the right information from your channel thank u again
adrolee1 (6 months ago)
Thank you! This helped me with understanding CTEs as well.
Semi Kolon (6 months ago)
Thanks
hymavathi g (7 months ago)
SQL query for " if we have two tables one is employee table, second manager table then need table like manager name column and employee names who are working under manager in one cell separated by comma" Give me a reply
Satyaki Bose (7 months ago)
top keyword not working on Oracle 11g.
OracleGrid (7 months ago)
1 select distinct top 2 sal 2 from emp 3* order by sal desc SQL> / select distinct top 2 sal * ERROR at line 1: ORA-00923: FROM keyword not found where expected its showing error .please help me ......
Kishor Tala (7 months ago)
Very well explained, thanks
MegaPruddy (7 months ago)
your voice is very soothing..
Nickk Bisht (7 months ago)
9:59 The reason why I love your videos. You explain each and every bit of coding.
Parveen Singla (8 months ago)
I learn new way to calculate nth highest salary and so simple
Abhishek Saha (8 months ago)
how to find 2nd highest salary except manager salary?
Youth Collections (8 months ago)
sir, tell me how to retrieve null values from multiple column like(20,40 col.) in sql.
Rohit Bari (8 months ago)
Select TOP 1 SALARY from Employees where SALARY in(Select TOP N SALARY from Employees ORDER BY SALARY DESC); IS this would work.
Wayne Richard (8 months ago)
Excellent...very helpful, thanks!
Dhaval Prajapati (9 months ago)
Can we write an outer subquery with where clause like 'where denserank=n'?
Renu Saraswat (9 months ago)
How split the data and display the output in different different columns. For ex- Hina Bhatt is a name in name column we want to show Hina in a different column and Bhatt in different column
vikas sharma (9 months ago)
It is simply wow....this is the first time I understood this answer. :-)
harshi kuppam (9 months ago)
please tell me how to copy results from one storedprocedure to another
Davood Mosaddar (10 months ago)
Great job. Thanks
ناصر سعود (10 months ago)
Hello good lessons How add +1 on the (id) in database , I do not want use Identity Specification , I want by code asp C# ??
vijay kumar kumar (10 months ago)
very nice explain Thanks
Susheel Singh (10 months ago)
Table variable vs Temporary table https://youtu.be/J7-7IWNKl1E
darpan waghchawre (10 months ago)
how to load specific record by dts in database ? can you please explan me
Leandro Augusto (10 months ago)
Amazing video and explanation! Very helpful. Thank you!
sravani 251094 (11 months ago)
How to find 3rd hight salary
leela krishna (11 months ago)
how to implement state management in MVC
GSH RDY (11 months ago)
In another video someone explained 2nd highest salary like this: select max(salary) from salary_table where salary not in (select max(salary) from salary_table) this works only for 2nd highest.
sunil Bhatraju (11 months ago)
is this sql server or sql query command?
Ahaiziah Shako (11 months ago)
Hi how do i get the max record with all its columns. for instance if you want the max payslip from the payroll table, but you want all the columns that come with that max payslip.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.