Find nth highest salary in sql server | sql query to find 2nd, 3rd highest salary
HTML-код
- Опубликовано: 4 авг 2024
- This video talks about the most asked SQL Server interview question :- Find the nth highest salary in SQL Server.
For more such videos visit www.questpond.com
For more such videos subscribe / questpondvideos
Check out our 50 most asked SQL Server interview questions watch this video • SQL Server Interview Q...
Question 1 :- Explain normalization ?
Question 2 :- How to implement normalization ?
Question 3 :- What is denormalization ?
Question 4 :- Explain OLTP vs OLAP ?
Question 5 :- Explain 1st,2nd and 3rd Normal form ?
Question 6 :- Primary Key vs Unique key ?
Question 7 :- Differentiate between Char vs Varchar ?
Question 8 :- Differentiate between Char vs NChar ?
Question 9 :- Whats the size of Char vs NChar ?
Question 10 :- What is the use of Index ?
Question 11 :- How does it make search faster?
Question 12 :- What are the two types of Indexes ?
Question 13 :- Clustered vs Non-Clustered index
Question 14 :- Function vs Stored Procedures
Question 15 :- What are triggers and why do you need it ?
Question 16 :- What are types of triggers ?
Question 17 :- Differentiate between After trigger vs Instead Of ?
Question 18 :- What is need of Identity ?
Question 19 :- Explain transactions and how to implement it ?
Question 20 :- What are inner joins ?
Question 21 :- Explain Left join ?
Question 22 :- Explain Right join ?
Question 23 :- Explain Full outer joins ?
Question 24 :- Explain Cross joins ?
Question 25 :- Why do we need UNION ?
Question 26 :- Differentiate between Union vs Union All ?
Question 27 :- Can we have unequal columns in Union?
Question 28 :- Can column have different data types in Union ?
Question 29 :- Which Aggregate function have you used ?
Question 30 :- When to use Group by ?
Question 31 :- Can we select column which is not part of group by ?
Question 32 :- What is having clause ?
Question 33 :- Having clause vs Where clause
Question 34 :- How can we sort records ?
Question 35 :- Whats the default sort ?
Question 36 :- How can we remove duplicates ?
Question 37 :- Select the first top X records ?
Question 38 :- How to handle NULLS ?
Question 39 :- What is use of wild cards ?
Question 40 :- What is the use of Alias ?
Question 41 :- How to write a case statement ?
Question 42 :- What is self reference tables ?
Question 43 :- What is self join ?
Question 44 :- Explain the between clause ?
Question 45 :-Subquery vs Correlated Queries
Question 46 :- Select the top nth highest salary using top and order by?
Question 47 :- Select the top nth highest salary using correlated and rownumber?
-----------------------------------------------------------------------------------------------------
For more such videos visit www.questpond.com
See our other Step by Step video series below :-
For more such videos subscribe / questpondvideos
35 Important JavaScript Interview Questions: • JavaScript Interview Q...
30 Important C# Interview Questions : • C# Interview Questions...
25 Important ASP.NET Interview Questions : • ASP.NET MVC Interview ...
25 Angular Interview Questions : • Angular Interview Ques...
20+ SQL Server Interview Questions : • SQL Server Interview Q...
10+ Power BI Interview Questions : • Power BI Interview Que...
20 PHP Interview Questions : • PHP Interview Question...
5 MSBI Interview Questions : • MSBI Interview Questio...
Learn Angular tutorial step by step tinyurl.com/ycd9j895
ASP.NET MVC Core Interview Questions with answers:- • ASP.NET MVC Interview ...
C# tutorial for beginners(4 hrs):- • C# Tutorial for Beginn...
Learn SQL Server Step by Step tinyurl.com/ja4zmwu
Learn Azure Step by Step:- • Azure Tutorial for Beg...
Azure AZ-900 fundamentals certification :- • AZ 900 Certification |...
AZ- 204 certification Azure:- • Azure 204 Certificatio...
Learn MVC 5 step by step in 16 hours:- • Learn ASP.NET MVC 5 ( ...
Learn Design Pattern Step by Step goo.gl/eJdn0m
Learn MSBI Step by Step in 32 hours:- goo.gl/TTpFZN
Python Tutorial for Beginners:- • Python Tutorial for Be...
Learn Data Science in 1 hour :- tinyurl.com/y5o7qbau
Learn Power BI Step by Step:- tinyurl.com/y6thhkxw
-----------------------------------------------------------------------------------------------
For more details :-
🌐 Website : www.questpond.com
📱 Mob. No. : +91-9967590707 / +91 7700975156 / +91-22-49786776
📧Email : questpond@questpond.com / questpond@gmail.com / questpond@yahoo.com
Share, Support, Subscribe and Connect us on!!!
20+ SQL Server Interview Questions : ruclips.net/video/SEdAF8mSKS4/видео.html
Software Architecture Interview Questions : ruclips.net/video/AtTgcbLOqMM/видео.html
SQL Step by Step - ruclips.net/video/uGlfP9o7kmY/видео.html
SQL Server Joins : ruclips.net/video/KTvYHEntvn8/видео.html
20 MSBI Interview Questions : ruclips.net/video/Nw_sHEKnOUE/видео.html
10+ Power BI Interview Questions : ruclips.net/video/Cozc9WNBRt4/видео.html
Angular Step by Step Tutorial for Beginners : ruclips.net/video/-9VcW7MBDs8/видео.html
25 Angular Interview Questions : ruclips.net/video/-jeoyDJDsSM/видео.html
35 Important JavaScript Interview Questions : ruclips.net/video/Zb4dPi7CANU/видео.html
30 Important C# Interview Questions : ruclips.net/video/BKynEBPqiIM/видео.html
25+ OOPS Interview Questions : ruclips.net/video/u99wAoBjDvQ/видео.html
25 Important ASP.NET Interview Questions : ruclips.net/video/pXmMdmJUC0g/видео.html
20 MySQL Interview Questions : ruclips.net/video/9hfjC-BpY20/видео.html
20 PHP Interview Questions : ruclips.net/video/1bpNSynUrl8/видео.html
5 MSBI Interview Questions : ruclips.net/video/5E815aXAwYQ/видео.html
Never got such a simple explanation, Thank you very much sir
Great content as always.
Very helpful. Thank you very much sir
So Nicely Explained sir..
very helpful, thank you.
One of cross question asked by interviewer is, if more than one records having same 2nd higest salary, query in video gives only one record
Write a CTE (Common Table Expression) using DENSE_RANK() function.
I was asked this question yesterday in the interview.
Your training is excellent
Bunch of thanks sir.🙏😊
Select * from employee E1 where n-1 = (select count(*) from employee E2 where E1.Salary < E2.Salary)
n = nth highest salary.
Thank you very much this was the easiest solution i could find on internet i'm glad i came across your comment
Very nice explanation as asual
In Oracle how can I find the same result. Top command will not work right?
Instead of row_number we can use dense_rank , bcoz if the table has duplicate 2nd highest salary and we nned to show both then it is not possible by row_number()
Fetch 1 offset n order by employeesalary should be the simplest way i think
Definitely a simple solution, but offset and limit is not ANSI SQL.From TSQL point of view its the simplest solution.
In PostgreSQL how to achieve?
the problem for me is that I don't want to get a job, because then I have to go to work... So I prefer to go unprepared to the interview, and then blame the company for xenofobia.
High 5 !
use rank and select where rank = 1
Rank is simple , LIMIT will more simple ,but these are TSQL. Will not work for other DB. In todays date with EF and Code first ANSI SQL has become more important. Thanks.
But if it is for 2nd highest salary and the table has duplicate top highest salary , Rank() will not give you exact results
Declare @N Int =2
Select EmployeeSalary
From (
Select Dense_Rank() Over (Order By EmployeeSalary) SNo, EmployeeSalary From TblEmployee) temp
Where SNo =@N
I have paid to join the channel but still I am unable to get access to videos.
Just make sure you are accessing membership videos from same google account from where you have made the purchase or you can contact youtube help center if you do not get the access.
Did u get the access?
Do u recommend it?
The video demonstrates well the idea but probably is not going to work if there are duplicated salaries in the table.
To solve this group by the salary in the sub query.
However using rank will be a better solution selecting the nth rank as the nth top salary.
In places where top or rank is not available an insert into temp table with autooncrement first column and sequential select the nth row from it will do too.
It surely works for duplicate , top secures it. Temp table would be a bad solution.
Thank you so much Sir
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN (
select distinct salary from Employee order by salary desc OFFSET @N-1 ROWS FETCH NEXT 1 ROWS ONLY
);
END