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

Part 1 How to find nth highest salary in sql

4231 ratings | 973539 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 (419)
Abel Mengistu (3 years ago)
Thank you for the resources, they are very helpful
Tech Reviews (7 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 (3 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
Vanish Kota (2 days ago)
second highest salary have two records (ben1,ben4) ,how to get in output both records??
ravi ranjan (3 days ago)
THANKS SIR
GOVIND MISHRA (3 days ago)
give video on how call one web api from another web api
Bhaskar Chittiboyena (8 days ago)
good one
Vinay Giri (8 days ago)
awesome
Robert Rusu (8 days ago)
You can get the highest salary by ordering DESC by salary, and doing a LIMIT N,1 and you use the offset to order, hence you don't need a sub-query. This assumes that you use a valid position, you can calculate the total rows also, and show a warning if N is invalid, because the row will return NULL :) And for duplicates you can just use DISTINCT
Zain Shaikh (11 days ago)
Mine is the 420th comment
raghu c (12 days ago)
I can write this in a more complicated way using If Else cond.
jeeva nandham (12 days ago)
What is the difference between count(*) and count(1) and what will happen give count(1)
Variety Stuff (15 days ago)
Thank you sir
Shahriar Rahman (15 days ago)
This video is 100% on point and saved my time ! Thanks a lot :D
Laxmender Gottipamula (16 days ago)
What if we use distinct for rownumber() can we get appropriate results
alireza nikoughadam (17 days ago)
Perfect
Sarah Lee (19 days ago)
Venkat, so grateful to your instructions! Your tutorials are comprehensive and inspiring, and get me away from fears coding the queries. I have followed 6 parts and will be keeping on learning. Thank you again!
kudvenkat (19 days ago)
Hello Sarah - Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. Good luck and all the very best with your programming career.
GOVIND MISHRA (22 days ago)
Give video on how consume web api in wcf
Maraxusofk (23 days ago)
Great video. Thank you for creating this!
Rahul Dophe (26 days ago)
I think 4 zeros on 8 is eighty thousand and not eight thousand.
Anon Rahman (27 days ago)
thanks
DEBASISH GHOSH (28 days ago)
Please have a video on extended stored procedure
365 Data Science (1 month ago)
Here are some frequently encountered SQL interview question, and my top tips to answering them: 1. What is SQL? SQL, Structured Query Language, is a programming language designed for working with databases. Some people, however, argue that it is not exactly a programming language since it has not been created with the notion of using features of procedural languages, such as conditional statements or “for” loops. These people insist on calling SQL a coding language because it is only about executing commands for querying, creating, inserting, updating, and deleting data in a database. Nonetheless, the most important thing is to know what the domain of SQL is. 2. What is a Database? What is a DBMS? A database is data stored on a computer and ordered in a way that makes it easy to access and manipulate. a database management system – DBMS is the software tool that allows users to interact with the data stored in the database. You could conclude by explaining there are two types of database management systems – relational and non-relational. SQL is a language, designed only for working with relational DBMSs. Such questions are usually asked in the beginning of the interview so that you can start preparing for the difficult ones. 3. What is the difference between DDL, DML, DCL, and TCL? First of all, let’s explain what there acronyms stand for: “L” stands for “Language” in all of them. And this must help you remember that these are the four categories in which the SQL commands have been separated into. DDL stands for Data Definition Language and includes the commands that allow you to CREATE, DROP, ALTER, and TRUNCATE data structures. DML involves commands for manipulating information. It actually means “Data Manipulation Language”, and gives the possibility to SELECT, INSERT, UPDATE, and DELETE data. it is this part of the language you will most use at work, if you are using SQL in the field of data science or business intelligence. DCL, Data Control Language, consists of commands that are usually used by database administrators. This one allows programmers to GRANT and REVOKE rights defining how much control you can have over the information in the database. Similarly, TCL, which is the Transaction Control Language, also includes the commands applied by database administrators. These command ensures that the danger of suffering from data loss is minimal. 4. What is the point of using a foreign key constraint? The foreign key constraint comprises a set of rules, or limits, that will ensure that the values in the child and parent tables match. Theoretically, the foreign key constraint will maintain the referential integrity within the database. 5. Define and provide an example of using an inner join. Obviously, you must be aware that joins are one of the most frequently used tools in SQL, regardless of your job role. Particularly if you are working in the sphere of business intelligence, your work will be centred around understanding SQL joins in depth. So, an SQL join is a tool that allows you to construct a relationship between objects in your database. Consequently, a join shows a result set containing fields derived from two or more tables. For instance, assume that in one table you have data about the customer ID and fields related to the sales a customer has made, and in the other, you have data about the customer ID and their private information, such as first and last name and email address. Therefore, an inner join allows you to obtain an output containing information from both tables only for the customer IDs found in the two tables that match. Provided that you set the customer ID field to be a matching column, of course. 6. What is the difference between MySQL and PostgreSQL? How about between PL/SQL and SQL? The interviewers ask these questions as they want to find out how much you are acquainted with the fact that SQL has a few versions, each carrying specific features. You could say that MySQL and PostgreSQL are just two versions of the Structured Query Language. Since you’ve just been asked about joins, you could mention that PostgreSQL supports outer joins, while MySQL doesn’t – you’ll need to use UNION or UNION ALL to emulate an outer join in MySQL. This way you could even impress the interviewers with extra knowledge in this subject. PL/SQL is not a version of SQL, however, and that’s the tricky part of the question. PL/SQL is a complete procedural programming language and its scope of application is different. It is not strictly related to relational databases. 7. What is an SQL View? Your potential future employers may want to give a toned-down SQL interview question. That’s why they might ask a general question. A view is a virtual table whose contents are obtained from an existing table or tables, called base tables. The retrieval happens through an SQL statement, incorporated into the view. In other words, you can think of a view object as a view into the base table. The view itself does not contain any real data; the data is electronically stored in the base table. The view simply shows the data contained in the base table. Good luck! Source: https://bit.ly/2Fe6x4Q Best, 365 Data Science Online educational career website https://365datascience.com/
Rupesh Deshaboina (1 month ago)
I wrote a query for this : Can you please check and let me know if it is correct -- 3rd highest salary select * from Employ where salary in ( select min(salary ) from Employ where salary in ( select distinct top 3 salary from Employ order by salary desc ) ) (or) select * from Employ where salary in ( select min(salary ) from ( select distinct top 3 salary from Employ order by salary desc ) ThirdHighestResult )
Vallabh Deshpande (1 month ago)
Awesome work 💪 bro. Simplified simply
SHILPA SINGH AMETHI (1 month ago)
NICE TUTORIALS HELPED ME A LOT ..THANKS
Rohi (1 month ago)
ID rating date 1 1. 01/02/2019 1. 2. 01/01/2019 2. 1. 04/03/2018 1. 3. 30/12/2018 O/p id. current_rating. prev_rating 1. 3. 2 1 2 1 2 1 1 1 1 0 ignore the "." how to get desired o/p???
Rohi (1 month ago)
@kudvenkat could u explain this?
Shivu S (1 month ago)
Superb broo
Ashutosh Singh (2 months ago)
You're Awesome!
DEEPTHI SREE VAMARAJU (2 months ago)
How to perform cumulative frequency in sql
Ritesh raj (2 months ago)
interviwer asked me 2nd highest salary ..
Milind Suryawanshi (2 months ago)
Very amazing explaination
Krishantha Dharmasena (2 months ago)
Sir, could you let me know , if you conduct the SQL class , if it’s yes , send me a message please
kudvenkat (2 months ago)
Hello Krishantha - For classroom and online training please contact us on [email protected] or +91 99001 13931.
Santosh Singh (2 months ago)
Is Microsoft SQL server is different from Oracle SQL?
kudvenkat (2 months ago)
Hi Santosh - Yes, Microsoft SQL Server Oracle are different databases. Most of the concepts and the SQL we use are almost very similar with very few differences. Hope this answers your question.
sathish v (2 months ago)
Cool
Akshay Acchi (2 months ago)
How to select n letters next to specific letter in a string by user defined function
Bindhyesh Tripathi (2 months ago)
Thank you sir for your amazing videos
Arvind Singh (3 months ago)
Nice and clear explanation . Good job!!
SAHUKARI NAVEEN KUMAR (4 months ago)
How would you print the first row of a table in dbms without using Select statement?
Yeezy (4 months ago)
Couldn't we use rank instead of dense rank? in rank the duplicate ones get skipped
Nijamuddin sayyed (4 months 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 (4 months ago)
everything is good. but it is not 6 or 7 thousand... infact it is sixty thousand and seventy thousand
Jyotsana Singh (5 months ago)
Excellent..explanation was clear
Sambit Pati (5 months ago)
Sir please make video tutorial for msbi and powerBI if possible
Inderjeet Singh (5 months 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 (5 months ago)
Sir ,having 3 year experience what kind of question will be ask for SQL ..
rakhshanda mujib (5 months 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 (5 months ago)
Splendid explanation...
Gamal Abdall (5 months ago)
This is just a beauty, I really loved the new Dense_Rank Function, kudvenkat you are the man. Thanks a million.
MARY MARGARET (5 months ago)
Ur voice is awesome
Ankita Tripathi (5 months ago)
sir , r u using 2016 sql
Ankita Tripathi (5 months ago)
Thank you..it is very important qun of an interview,which i was looking for that
Nawal Tarek Matarid (5 months ago)
OMG why did I just discover your channel... I have so much to catch up on.... *Goes on to watch all your vids*
S.v. Kutty (5 months ago)
Chance eila very nice thanks lot
Durga Mallesh (5 months ago)
Why Top command is not working in my system ?
Bakari Tijjani a (6 months 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 (6 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 (6 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 ?
Kavitha Komuravelli (6 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 (6 months ago)
Hi Vinkat, please upload the video for alternate of Union/Union All
Uma Tripathi (6 months ago)
How to write a query for fetching a record inserted yesterday.
Amit Goel (6 months ago)
Sir, can we query the same questions without using sub query?
Akshatha a (6 months ago)
Very well explained thank you so much😊
Ram Kris (6 months ago)
Will this work with oracle, i dont think so as the TOP clause is not supported in oracle
Karan Dang (6 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.
Abdul (6 months ago)
It is very clear.thanks lot
Taffessech Tessema (6 months ago)
Thanks a lot cleared my confusion
monica vyas (6 months ago)
very nice, could you please put more related questions
Veerendrakumar Prajapati (6 months ago)
I faced more complex like join department table and find department wise nth number of salary
Anurag Kushwaha (7 months ago)
TOP clause is not working in oracle 12c ????? can you please tell me why but you used TOP clause
poorna pragna (7 months ago)
Thank you very much sir, Pls let us know 'how to find duplicate row in a table' ?
poorna pragna (7 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 (4 months 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 (7 months ago)
Excellent One sir thanks teaching nth highest salary using Sub-Query !!
jayavardhan naidu (7 months ago)
how can we add column in a table in stored procedure at the time of execution in sql server
sai venkat prakash (7 months ago)
Tq very much for your good explanation
Tech Reviews (7 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 (7 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 (7 months ago)
can you please share all .Net and Sql interview Series links.....
Harsh Vardhan Dewari (7 months ago)
can we use OFFSET and FETCH to get nth highest?
Thank you very much sir!!
Paul D (8 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 (8 months ago)
Top keyword is not working..error showing
zulqadar idrishi (8 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 (8 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 (8 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 (8 months ago)
SELECT Name, Salary FROM Employee WHERE Salary = (SELECT Max(Salary) FROM Employee)
Prasad (8 months ago)
Thank you so much sir for great help to learn technology.
Uday Kiran (8 months ago)
Awesome
HARSH (8 months ago)
Can anyone please tell me how to give the table name dynamically in SELECT query. Thanks in advance.
Mokhlesur Rahman (9 months ago)
So much helpful and learnable all of your's video ... but sound is so low to understand, please
mohsen rezvani (9 months ago)
if you use partition by in your Row_num function it'll work fine
Siddharth Khade (9 months ago)
why is the need to sort in the subquery, if the data is again sorted outside it
Mahendra Chandekar (9 months ago)
How can we get 7th high salary without using aggregate function,rownum?
Mahendra Chandekar (9 months ago)
How can we get 7th high salary without using aggregate function,rownum?
Ravi Chandra kancharlah (9 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 (9 months ago)
My 2nd question: How to find name of all employees having 2nd highest salary?
pratik chhapolika (9 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 (9 months ago)
thank for this explaination, nice
Nikhil Jilhawar (9 months ago)
👌👌👌👌👌
Gaurav Mishra (9 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 (10 months ago)
You got an amazing voice...helps to learn quickly
Rajesh Gaikar (10 months ago)
Simple elegant and Great explanation..

Would you like to comment?

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