Part 1 How to find nth highest salary in sql

[@AbelMengistuweb]

Thank you for the resources, they are very helpful

[@Csharp-video-tutorialsBlogspot]

+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 kzbin.infoplaylists?view=1&sort=dd You can also order DVDs or download all the videos and slides for offline viewing using the link below www.pragimtech.com/kudvenkat_dvd.aspx Image version of the slides and text Version of the videos are on my blog csharp-video-tutorials.blogspot.com Tips to effectively use our free channel. kzbin.info/www/bejne/r2ibYYCtnb5qZtU To receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. kzbin.info 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

[@techspace5642]

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

[@danieltaye6566]

@@techspace5642 It doesn't. 'cause min pulls the minimum by value not by order

[@brackishdreams]

Thank you so much

[@mangorez0]

6 years passed from the video posted in May 2014. And today in May 2020 I still found it very helpful. Great job done for explanation and preparation. It is the best tutorial I have ever met in video, plus it has links to the slides and code. BRAVO! TOP JOB!

[@arunkaiser]

I am from 2023 still found it useful

[@TheSourav1986]

@@arunkaiser Now I am fin 2024 still found relevant

[@kripamishra1001]

Still in 2024...

[@amanahmed6057]

today in sept 2024 also

[@rohitpatil1993]

When it comes to SQL learning ..Your Channel is the only thing comes to my mind. Really appreciate your hard-work.

[@mirrapriya]

Hi Venkat , I really appreciate your effort in creating all these tutorials.It helped me to get 2 job offers.Your explanations and examples are very vivid.Thanks for your time and effort.Keep going !! I have been recommending your channel to many friends and will keep doing it.

[@Csharp-video-tutorialsBlogspot]

+mirrapriya Wow....Congratulations. Very happy for you. I am honored, when you say these videos helped you to get the job. Thank you very much for taking time to share the good news. Good luck and all the very best with your new role.

[@sirsyed3502]

+kudvenkat hi sir hope u well i have a question i have a website name web1 and database my website is live and hosted on domain i have another website web2 also live n hosted both database are different my task is that perform some search in web2 and the the result will be as the stored question and answer in web1 database it goes on web1 database n shown it on web2 page to retrieve the search result as google search technique its crawler or google cse technique please kindly guide me

[@codewithcreativecoder8834]

it's 2024 and this video is still helpful for me. Thank you so much kudvenkat

[@gokulm594]

yep still in big 4 they ask these type of questions haha

[@samiranroyy1700]

should i do sql practices from this video?

[@codewithcreativecoder8834]

@@samiranroyy1700 yes

[@ga7853]

This is just a beauty, I really loved the new Dense_Rank Function, kudvenkat you are the man. Thanks a million.

[@shirshodatta2886]

Last part where you showed the difference with row_number, it was a great finisher indeed. My concept got clear about the ranking functions. Thanks a lot @kudvenkat

[@shantilal_suthar]

I got first job by following your videos for learning.... and its been 2 years experience and still following your videos so that can get better opportunity. I am sure its gonna work again...XD

[@prasadm1117]

Bro please help me wr r u Staying I'm looking for job for SQL Server DBA

[@rituagrawal3950]

Shantilal Suthar hi

[@TableauTip]

kzbin.info/door/MyjwXfdnf1ezjJyUDHXrsA

[@hanifhani1]

same here

[@rajasekharreddy478]

Iam Raja Sekhar recently i watched your vedios about SQL for beginner's it's very useful to me. Full explanation it's amazing ,it's easy to understand .thanks lot for ur helping the viewer by giving free knowledge to us .once thanks for alot.

[@1256dimple]

the way you explain any concept is simply amazing! thank you so much!

[@sarahlee1877]

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!

[@Csharp-video-tutorialsBlogspot]

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.

[@rajeshmanivannan5313]

This is very useful video, I got this question in the interview > How to find the 75th highest salary record in an employee table where there are 1000 records.

[@poojagore2582]

Watching this vedio after 9 years, still this question is asked in every interview ... Thank you for explaining in such a giod way 😊

[@shanks24a3]

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.

[@vishnuvamshi4645]

THANKS ALOT CLEARED CONFUSION.... BEST PART OF YOUR VIDEO IS GIVEN COMPLETE DETAILS FROM TABLE CREATION TO EXECUTION IN DESCRIPTION WHICH SAVES TIME

[@letmeexcel5943]

Kud Venkat is Good Venkat. Thanks for all the efforts you have put in to make these videos.

[@humanitylover792]

Your way of explanation is amazing and unique Thank you so much because you made my skills much polished from zero to hero. I was unable to prepare anything but you made me the most confident one. Thanks once again

[@pgadige]

Thank You Venkat for a detailed explanation, we can also use the row number when we have duplicate values using the below query with Result as ( select salary,row_number() over (order by salary desc) as "Row Number" from (select distinct Salary from Employees) DisEmp ) select * from Result where Result.[Row Number] = 3

[@tauhidhussain8629]

great 👍😊

[@poomanivenugopal3193]

simply superb.. i got lot of confusions before while these kind of queries... now got it.. really super and Thankyou so much...

[@nonoobott8602]

You're a genius. I've been searching for this. Your explanation is everything. Thanks for sharing

[@JymVer]

best video of this nature and ive watched a few today. Well done sir

[@beautyofnature6709]

Amazing Teaching Technique I Learned within min My All doubts Clear within min

[@meghabhatnagar3784]

I was given these two tables:- (A) month, category, primeflag, shippedunits (B) month, primeflag, shipcost I was asked what assumptions would you make? If you wanted to write a select into statement for the following table what would you write:- month, category, primeflag, shippedunits, shippedcost

[@ahmadganteng7435]

Best SQL Server course I ever had.. Thanks Venkat

[@MrJonnis13]

Superb tutorial, as usual. Just an alternative though: SELECT MIN(salary) FROM dbo.Employees WHERE salary IN(SELECT DISTINCT TOP N salary FROM dbo.Employees ORDER BY salary DESC)

[@spicytuna08]

i don't think you need desc operator. min() will find the minimum.

[@krismaly6300]

I revisited this video to refresh my mind. I enjoyed watching this video and recommend others to watch. Topic has been explained in crisp and clear. Thanks Guru Venkat

[@sagarpanda645]

>select ename,sal from emp where sal = ( select max(sal) from emp where level=&levelno connect by prior sal>sal group by level;

[@KhalidAfridi1]

Thanks Venkat, Excellent You are awesome dear, not only this one but all the other videos specially the SQL are fabulous and very much informative and very easy to understand. Your way of teaching is very much understandable and easy. We have learnt a lot from your video series on all the programming fields C#, SQL Server etc. Be blessed and thanks a lot.

[@sonikagoyal9479]

Thankyou Venkat . You can make anyone to get offer letter with nice packages . I am so confident today just because of you

[@Csharp-video-tutorialsBlogspot]

Wow! That's great news Sonika. Will be delighted to know if you made it. Thank you for sharing.

[@delmensah3544]

I have four questions can you help with this please 1. What’s the largest dataset you have worked upon in the past? 2. How can you find an abstract book from personal library? 3. How do you find a record is from which table? 4. What is a query plan?

[@MrTrojis]

To add; for the CTE we would not want to use RANK as opposed to DENSE_RANK because RANK would skip rankings for duplicate salary values. For example if we were to use RANK we would skip over rank 3 because there are two values for 70,000 in salary and searching for the 3rd highest salary would yield nothing

[@1MYNAMEISKHAN]

more simpler and generic query would be:- select salary from employee e1 where n=(select count(distinct e2.sal) from employee e2 where e2.sal>=e1.sal);

[@krismaly6300]

I enjoyed watching this video and I recommend others to watch since it has been clearly expressed concept Very helpful in interview and also in real life

[@gardeningforeveryone8585]

Still useful in 2021. Very well explained. Thank you🙏

[@himanshurustagi7351]

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.

[@aravinds3808]

Thanks guys.. your videos are awesome.. U also helped me to land SQL developer job..

[@AbdulWahab-li9ri]

Hello Venkat, I am thank full to you that you create a great tutorial and yes this is the only tutorial which lead me to a great job. The efforts you made are very helpful for many peoples. You Rocks Man.

[@miteshjain56]

Best teacher . Please make more videos . Thank you so much sir. Very helpful videos . Nice explanation.

[@riyankabhowmick697]

I became fan of you... You explain everything so nicely...

[@tarapanthi9615]

your videos are very effective n helpful for beginners...I follow your tutorials to study IT.Thank you.

[@SunilChintu-sh2ct]

Thank you, you are providing good SQL interview question & Answer ,It's was helped to me, i have crack the interview.

[@poornaps]

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

[@ganeshmhaske49]

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.

[@deeptigautam9713]

Hi Venkat. Can you please make a video on 1). finding growth percentage for each item over the months [ex. column name can be: month, item, quantity] and 2). find the personid who appeared for the appointment 3 time continuously [ex. column name can be: date, person_id, status]

[@dattakashiwale]

Excellent.... Ek Number... Concept Cleared....

[@venureddy8218]

what an explanation! hats off... you got very good communication

[@balrandhawa7128]

Awesome teaching technique. Very useful channel. Keep up the good work.

[@AbhishekAbhi-mc1dv]

I really liked your way of explaining the query ...appreciate thanks

[@shreyaspatil24]

thank you. another way for nth salary is to use limit select salary from (select salary from employee order by salary desc limit n ) as newTable order by salary limit 1;

[@varma8774]

Hi Venkat. Am thanking you for offering these tutorials, ur providing a very good explanation, really it's helps a lot! i am sharing one of the interview question which i was faced! Can you plz make a video session for clear explanation ---How to avoid Divide by zero error ? also i wanted to show the null value results as NULL is that possible ? Create table tblcompute ( Numerator int not null, Denominator int not null, Result as (Numerator / Denominator) ) Go insert into tblcompute values (3,0) when we issue a select querry Select * from tblcompute Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. even when we add new rows also getting the same error insert into tblcompute values (10,2) Finally i come up with these querys! Select * from tblcompute where Result !=0 SELECT Numerator , Denominator, NULLif(Result, 0) as Results from tblcompute SELECT Numerator , Denominator, NULLif(Result, null) as Results from tblcompute Can you plz give me an in depth explanation for this scenario !

[@yamaya5136]

Still helpful, 2022. My favorite teacher by far.

[@hopeislife1651]

Clear explanation, thank you, have a pleasant day sir

[@arvindkumartegampure4170]

By Referring your videos this also can be done by offset. to Find 4th highest. select distinct salary from tblEmployees order by salary desc offset 3 rows fetch next 1 rows only

[@vishaljaiswar3913]

you are awesome ...👍👍🙏🙏 respect man..just because your way of explaination is to step by step which becomes easy to understand thanx man..

[@Dk-gl7rq]

I watched many lecture but finally i understand subquery concept

[@geoffluw]

Bro, you have explained it so well. Perfect!!

[@vijayvenkat96]

We can also use Min(Salary) instead of Top 1 while using sub query but both will give same result.... Something like below Select MIN(Salary) from (Select distinct top 15 (N-can be any number) Salary from Employee Order by Salary desc) Result

[@yaarkhakf]

A very simple sub query would be, Select * from Employees where SALARY = (SELECT MIN(SALARY) from (select DISTINCT TOP 2 salary from Employees ORDER BY SALARY DESC) as result )

[@ilyasnaghiyev7038]

WHAT IF HIGHEST SALARY DUPLICATED 10 TIMES?THEN TOP 2 WILL BE STILL HIGHEST SALARY.

[@yaarkhakf]

No it won't because of the 'distinct' keyword.

[@nawaltarekmatarid8880]

OMG why did I just discover your channel... I have so much to catch up on.... *Goes on to watch all your vids*

[@MasterjiClassy]

It is simply wow....this is the first time I understood this answer. :-)

[@nitincsawant]

Hats off to the way you explain...thank you very much Venkat.

[@aritrachatterjee487]

using ROW_NUMBER() we can achieve the result by adding Group By clause : WITH RESULT AS ( SELECT SALARY, ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER FROM EMPLOYEES GROUP BY SALARY ) SELECT SALARY FROM RESULT WHERE ROWNUMBER = 3 Correct me if I'm wrong.

[@robertuniqid]

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

[@chamelion1red]

Simplest example*** _________________________ Suppose for 2nd Highest Salary 1) Select max (sal) from employee where sal NOT IN (select max (sal) from employee) 2) select * from employee E1 where 2= (select count(distinct(E2.sal))from employee E2 where E2.sal >=E1.sal

[@vijaydubey1734]

Great Sir, a true professional as well as a proficient person with awesome convincing skill (y)

[@sreecreativy]

With result as ( select salary ,row_number() over ( partition by salary order by salary desc) as rownumber from employee) t Select salary from result.rownumber =2 ; Sir this will work with row number as well if we use partition by

[@shamsularifeen166]

You can get the result in a more simple way by using LIMIT keyword, For example getting top 5 high salaries (SELECT * FROM employees ORDER BY salary DESC LIMIT 5)

[@ayandey4742]

Thank You very much Sir. Your videos are better than some paid courses.

[@ryanmariner]

Very helpful and very clearly explained! Thank you for making this video. Please keep up the good work!

[@rubanprakash8088]

Brilliant explanation. Keep up the good work.

[@shekharmishra9903]

Thanks Kudvenkat for your clear description.

[@NancyMancarious]

Thinking about it, I'd prefer to use DISTINCT w/ LIMIT.. OFFSET with the sub-query instead of writing two ORDER BY statements. And a sub-query's function here would be to pull the data related to that particular salary.

[@Krranganathakr]

Hi Venkat, I am thank full to you that you create a great tutorial and yes this is the only tutorial which lead me to a great job .. Can you please tel how we can swap row date in single table using only select statement , This is one of interviewer question . Ex -- in table have 5 row . 1 - ranga , 2- Venkat , 3 - Manoj ,4 - Ranga 5- Venkat , we need to swap where ever we find ranga and next to that venkat we need to swap output like venkat next ranga .

[@ga7853]

Venkat is the man, too bad we do not hear him any more.

[@gshrdy5415]

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.

[@sanjarmatin6227]

Awesome man ,crystal clear and crisp !!!

[@tothedust]

Dear sir what is the purpose of ldf file or transaction log and how to find duplicate data in SQL in more than one columns using having clause.

[@rajeshgaikar7535]

Simple elegant and Great explanation..

[@rashmi933]

Hi Venkat , You are really wonderful , Thanks for helping us , May God bless you.

[@sabyasachisahu7663]

select sal from (select sal, dense_rank() over (order by sal desc) as a from emp) where a = n* n* is the nth no.

[@amkytube5242]

You are the best one. Keep Going bro.

[@ennkaybee24]

we can actually do this using row_number() SELECT salary FROM ( SELECT salary, ROW_NUMBER() OVER(ORDER BY salary DESC) as rownum FROM ( SELECT DISTINCT salary from Employees ORDER BY salary DESC ) AS DISSAL ) AS OuterGrid WHERE OuterGrid.rownum = @NthPosition

[@nazishsiddiqui3558]

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

[@pradeepnara7718]

Hi Venkat, how to delete the duplicate records in a table?.....This is one of the most common question in face in the interviews.

[@venkateswarulukudithipudi1053]

Hi Pradeep, you are 100% correct, very common sql server interview question. Thanks for asking. I will record and upload a video very soon. If you have any other sql related interview questions, please feel free to leave them here.

[@Csharp-video-tutorialsBlogspot]

Hi Pradeep, you are 100% correct, very common sql server interview question. Thanks for asking. I will record and upload a video very soon. If you have any other sql related interview questions, please feel free to leave them here.

[@poornaps]

Excellent One sir thanks teaching nth highest salary using Sub-Query !!

[@gokukanishka]

We can use rows between unbounded precedings and current To handle duplicate values

[@saisai-yo4nv]

We can also use this method SELECT name, salary FROM employee e1 WHERE N-1= (SELECT COUNT(DISTINCT salary) FROM employee e2 WHERE e2.salary > e1.salary);

[@LetCode96666]

This video is 100% on point and saved my time ! Thanks a lot :D

[@bikashpadhy9462]

Sir, Kindly make video on indexes, I am asked in interviews about materialized views, Kind of indexes used, stored procedure practically, Kindly make videos on Realtime scenarios approaches... Will be helpful Thank you

[@ArvindSingh-hk8bt]

Nice and clear explanation . Good job!!

[@vipulzambare5329]

sir,these all tutorials are really nice... HATS OFF!!!

[@sonderis]

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

[@danieltaye6566]

I think it doesn't work. 'cause there is no 'salary' column in the cte

[@sonderis]

@@danieltaye6566 The cte contains all columns from employees in addition to the dense rank. see the asterisk?

[@danieltaye6566]

@sonderis got it thanks buddy.

[@HabibAntoun-ws3vo]

SQL Tutoring = Venkat 😍

[@rajatjoshi6695]

You got an amazing voice...helps to learn quickly

[@lijessica8392]

THIS IS A VERY HELPFUL VIDEO, YOU DID GREAT JOB,THANK YOU FOR SHARING YOUR KNOWLEDGE.

[@LearningwithIzaan]

Can you please do SAS tutorial....I havnt found a single youtube tutorial that helped me. I find your tutorials very clear and to the point.Please upload SAS ...Please...:)

[@krismaly]

Wow thre functionalities explained with example clearly. DENSERANK, CTE, ROW_NUM. Thanks a lot

[@a-bug-nerd]

Thank you! This helped me with understanding CTEs as well.

[@saylishah6708]

Hi Venkat. Thanks for the video. I have a doubt here. For finding the 2nd highest salary, you have first order by Sal Desc in subquery and then in main query you have order by Sal Asc. Instead of 2 steps why dnt we directly Order by Sal Asc in sub query? Can we do that?

[@pardhasaradhi0076]

Iam pardhu. I have the a question as shown below: -- what is the diffrerences between sql server 2000,2005,2008,2008R2, 2012,2014 . I didn't find any exact document with practical example.If you upload such kind of video it really help full for beginners.

[@sunilkumarkashyap199]

Hi Venkat! we are learning too much from you....and it is very helpful. thanx for sharing.. could you please share more tutorials on sql (advance sql). thnx again.