Declare @N Int =2 Select EmployeeSalary From ( Select Dense_Rank() Over (Order By EmployeeSalary) SNo, EmployeeSalary From TblEmployee) temp Where SNo =@N
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()
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.
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.
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.
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 !
20+ SQL Server Interview Questions : th-cam.com/video/SEdAF8mSKS4/w-d-xo.html
Software Architecture Interview Questions : th-cam.com/video/AtTgcbLOqMM/w-d-xo.html
SQL Step by Step - th-cam.com/video/uGlfP9o7kmY/w-d-xo.html
SQL Server Joins : th-cam.com/video/KTvYHEntvn8/w-d-xo.html
20 MSBI Interview Questions : th-cam.com/video/Nw_sHEKnOUE/w-d-xo.html
10+ Power BI Interview Questions : th-cam.com/video/Cozc9WNBRt4/w-d-xo.html
Angular Step by Step Tutorial for Beginners : th-cam.com/video/-9VcW7MBDs8/w-d-xo.html
25 Angular Interview Questions : th-cam.com/video/-jeoyDJDsSM/w-d-xo.html
35 Important JavaScript Interview Questions : th-cam.com/video/Zb4dPi7CANU/w-d-xo.html
30 Important C# Interview Questions : th-cam.com/video/BKynEBPqiIM/w-d-xo.html
25+ OOPS Interview Questions : th-cam.com/video/u99wAoBjDvQ/w-d-xo.html
25 Important ASP.NET Interview Questions : th-cam.com/video/pXmMdmJUC0g/w-d-xo.html
20 MySQL Interview Questions : th-cam.com/video/9hfjC-BpY20/w-d-xo.html
20 PHP Interview Questions : th-cam.com/video/1bpNSynUrl8/w-d-xo.html
5 MSBI Interview Questions : th-cam.com/video/5E815aXAwYQ/w-d-xo.html
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
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.
Never got such a simple explanation, Thank you very much sir
Really well explained topics.. I got new interest in learning .. Thank you..
I was asked this question yesterday in the interview.
Declare @N Int =2
Select EmployeeSalary
From (
Select Dense_Rank() Over (Order By EmployeeSalary) SNo, EmployeeSalary From TblEmployee) temp
Where SNo =@N
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()
Your training is excellent
So Nicely Explained sir..
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.
Very helpful. Thank you very much sir
Very nice explanation as asual
Bunch of thanks sir.🙏😊
very helpful, thank you.
In Oracle how can I find the same result. Top command will not work right?
Great content as always.
Thank you so much Sir
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
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.
In PostgreSQL how to achieve?
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 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 !
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