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

Part 1 How to find nth highest salary in sql

4798 ratings | 1051858 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 (441)
Abel Mengistu (3 years ago)
Thank you for the resources, they are very helpful
Daniel Taye (22 days ago)
@Tech Reviews It doesn't. 'cause min pulls the minimum by value not by order
Tech Reviews (10 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
Hari Prasad (10 hours ago)
Hello sir, I thanks for providing all the information. I have few questions to ask here I will ask one.how do we come to which type index we need to create
Saif Hassan (23 hours ago)
how to find name of highest salary paid employee?
Pramod Patane (21 hours ago)
select name as Highest_Salaried from employee where salary=(select MAX(salary) from employee);
Manguesh Pednekar (3 days ago)
I want to find highest salary of employee per month
Can I update a view?
Rohan Rao (9 days ago)
Great video! Thanks Venkat :)
data science (9 days ago)
what is the "result" that you mentioned..it have to be included?
Aapu dey (12 days ago)
But i want to Show all col not only salary col. Then how ? Suppose i have many col like around 20 col.s of a big table then how ... Like use of * to get all col value for only 3rd highest salary record. please help me
Shivangi Rai (17 days ago)
How to fetch alternate record from table ie both odd and even row numbers
Nazish Siddiqui (19 days ago)
Hi I have a question There are 5 teams (a,b,c,d&e) each team will only one match against each other eg if a and b have played then they will not play again. Result table will be like this A B A C A D A E B C B D B E C D C E D E Write a SQL query to show the following output
sonderis (23 days ago)
with cte as ( select dense_rank() over(order by salary desc) as Rank#, * from employees ) select distinct salary from cte where Rank# = 3 ^This makes for the quickest approach to me, coding wise. Not sure on performance...
Daniel Taye (12 days ago)
@sonderis got it thanks buddy.
sonderis (13 days ago)
@Daniel Taye The cte contains all columns from employees in addition to the dense rank. see the asterisk?
Daniel Taye (22 days ago)
I think it doesn't work. 'cause there is no 'salary' column in the cte
Vipin Perkawar (26 days ago)
Sir i faced sql interview question Q. how to convert male to female and female to male in table of columns id,name,gender.. Sir they told me not to use update statement. how can i solve this ?
NanDini Sushil (26 days ago)
Saw this question some where.! Write SQL query to find the user who logged in maximum number of times (table has entries with username, timestamp?) Can you answer this!?
Engr Taffy (11 days ago)
SELECT username, COUNT(username) as value FROM login Group by username order by value desc limit 1
Potla Rambabu (1 month ago)
If I run the below query it gives error SELECT TOP 1 SAL FROM (SELECT DISTINCT TOP 2 SAL FROP EMP ORDER BY SAL DESC) RESULT ORDER BY SAL.. I'm Running on Oracle SQL developer.Is this work on or not?
Potla Rambabu (1 month ago)
SQL error 00923
Potla Rambabu (1 month ago)
SELECT TOP 1 SAL FROM (SELECT DISTINCT TOP 2 SAL FROP EMP ORDER BY SAL DESC) RESULT ORDER BY SAL
DINESH RAJ (1 month ago)
Well explained 👍
Kashyap Tech (1 month ago)
Thanku so much sir for this all videos on SQL server ! I am one of employee working in TCS in Mumbai and I am teaching in classes and used your videos to teach other students and also making my personal video in Hindi Language. Link which is in hindi language tried to make best :- https://youtu.be/ITg-eZHiRRA Please provide your feedback on this 😊
srinu bathina (1 month ago)
can you please make a video on department wise third highest salary
anil garmilla (1 month ago)
how to change status column value if 0 is there change to 1 vice versa
atul sharma (1 month ago)
can we do like this for getting nth highest salary select min(salary) from(select distinct top <n> salary from employee )
raju raju (1 month ago)
Super explanation
Threelly AI (1 month ago)
Oops https://chrome.google.com/webstore/detail/threelly-ai-for-youtube/dfohlnjmjiipcppekkbhbabjbnikkibo
Threelly AI (1 month ago)
Yikes https://chrome.google.com/webstore/detail/threelly-ai-for-youtube/dfohlnjmjiipcppekkbhbabjbnikkibo
Nitin Gupta (1 month ago)
Thank you for the video! How can we get the Lowest 3rd Salary or the Nth Lowest Salary from the Above Table? Thanks
Neeraj Raghav (1 month ago)
Difference between exe and DLL?
Dharmendra Singh (1 month ago)
Quite Help Full, Nice one Thanks.....
keyur panchal (2 months ago)
DECLARE @nthSalary INT = 2 Select * from Employee e1 Where @nthSalary = (Select Count(Distinct salary) from Employee e2 where e1.Salary < e2.Salary)
Rejin k p (2 months ago)
How to find the department in the column more than 5 times.
Abhishek Abhi (2 months ago)
I really liked your way of explaining the query ...appreciate thanks
NIIT NH (2 months ago)
My Question is why do we prefer SUB-QUERY on JOINS or the difference between SUB-QUERY and JOINS
Bhushan Bhasme (2 months ago)
Hello sir, I got an interview question like what is Rank(),Dense_Rank() ,Row number,PIVOT in sql?
Himanshu Rustagi (2 months ago)
Hi Venkat, I have recently come across your videos. these videos are actually gold mines. Thank you very much for explaining is such a simple way.
Fari Noor (2 months ago)
I like the way you explained it.
New Life Loading (2 months ago)
where is part 2
anant sharma (2 months ago)
why did we use a name (Result) for the output of sub query? running the complete query without "Result" executes but fails otherwise. why?
Vanish Kota (2 months ago)
second highest salary have two records (ben1,ben4) ,how to get in output both records??
ravi ranjan (2 months ago)
THANKS SIR
GOVIND MISHRA (2 months ago)
give video on how call one web api from another web api
Vinay Giri (3 months ago)
awesome
Robert Rusu (3 months 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 (3 months ago)
Mine is the 420th comment
raghu c (3 months ago)
I can write this in a more complicated way using If Else cond.
jeeva nandham (3 months ago)
What is the difference between count(*) and count(1) and what will happen give count(1)
Variety Stuff (3 months ago)
Thank you sir
Shahriar Rahman (3 months ago)
This video is 100% on point and saved my time ! Thanks a lot :D
Laxmender Gottipamula (3 months ago)
What if we use distinct for rownumber() can we get appropriate results
alireza nikoughadam (3 months ago)
Perfect
Sarah Lee (3 months 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 (3 months 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 (3 months ago)
Give video on how consume web api in wcf
Maraxusofk (3 months ago)
Great video. Thank you for creating this!
Rahul Dophe (3 months ago)
I think 4 zeros on 8 is eighty thousand and not eight thousand.
Anon Rahman (3 months ago)
thanks
DEBASISH GHOSH (3 months ago)
Please have a video on extended stored procedure
365 Data Science (3 months 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 (4 months 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 (4 months ago)
Awesome work 💪 bro. Simplified simply
SHILPA SINGH AMETHI (4 months ago)
NICE TUTORIALS HELPED ME A LOT ..THANKS
Rohi (4 months 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 (4 months ago)
@kudvenkat could u explain this?
Shivu S (4 months ago)
Superb broo
Ashutosh Singh (4 months ago)
You're Awesome!
DEEPTHI SREE VAMARAJU (5 months ago)
How to perform cumulative frequency in sql
Ritesh raj (5 months ago)
interviwer asked me 2nd highest salary ..
Milind Suryawanshi (5 months ago)
Very amazing explaination
Krishantha Dharmasena (5 months ago)
Sir, could you let me know , if you conduct the SQL class , if it’s yes , send me a message please
kudvenkat (5 months ago)
Hello Krishantha - For classroom and online training please contact us on [email protected] or +91 99001 13931.
Santosh Singh (5 months ago)
Is Microsoft SQL server is different from Oracle SQL?
kudvenkat (5 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 (5 months ago)
Cool
Akshay Acchi (5 months ago)
How to select n letters next to specific letter in a string by user defined function
Bindhyesh Tripathi (5 months ago)
Thank you sir for your amazing videos
Arvind Singh (6 months ago)
Nice and clear explanation . Good job!!
SAHUKARI NAVEEN KUMAR (7 months ago)
How would you print the first row of a table in dbms without using Select statement?
Yeezy (7 months ago)
Couldn't we use rank instead of dense rank? in rank the duplicate ones get skipped
Nijamuddin sayyed (7 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 (7 months ago)
everything is good. but it is not 6 or 7 thousand... infact it is sixty thousand and seventy thousand
Jyotsana Singh (7 months ago)
Excellent..explanation was clear
Sambit Pati (7 months ago)
Sir please make video tutorial for msbi and powerBI if possible
Inderjeet Singh (8 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 (8 months ago)
Sir ,having 3 year experience what kind of question will be ask for SQL ..
rakhshanda mujib (8 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 (8 months ago)
Splendid explanation...
Gamal Abdall (8 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 (8 months ago)
Ur voice is awesome
Ankita Tripathi (8 months ago)
sir , r u using 2016 sql
Ankita Tripathi (8 months ago)
Thank you..it is very important qun of an interview,which i was looking for that
Nawal Tarek Matarid (8 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 (8 months ago)
Chance eila very nice thanks lot
Durga Mallesh (8 months ago)
Why Top command is not working in my system ?
Bakari Tijjani a (8 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 (9 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 (9 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 (9 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 (9 months ago)
Hi Vinkat, please upload the video for alternate of Union/Union All
Uma Tripathi (9 months ago)
How to write a query for fetching a record inserted yesterday.
Amit Goel (9 months ago)
Sir, can we query the same questions without using sub query?
Akshatha a (9 months ago)
Very well explained thank you so much😊
Ram Kris (9 months ago)
Will this work with oracle, i dont think so as the TOP clause is not supported in oracle
Karan Dang (9 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.
It is very clear.thanks lot

Would you like to comment?

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