Part 1 How to find nth highest salary in sql - - vimore.org

Part 1 How to find nth highest salary in sql

Part 1 How to find nth highest salary in sql

YouTube

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



Part 2 SQL query to get organization hierarchy

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

YouTube

c# (Csharp) interview questions :- What is the use of private constructor ?

For more such videos visit http://www.questpond.com See our other Step by Step video series below :- Learn C# Step by Step in 100 hours :- https://goo.gl/FNlqn

YouTube

Part 4 Delete duplicate rows in sql

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

YouTube

Joins in sql server - Part 12

In this video we will learn about 1. The different types of Joins in sql server 2. Cross Join 3. Inner Join 4. Outer Join -- Left , Right and Full Outer Join.

YouTube

What are your salary expectations? (English) : Job Interview Question

How to answer job interview questions "What are your salary expectations".

YouTube

ASP NET Core LogLevel configuration

In this video we will discuss the significance of LogLevel configuration in ASP.NET Core. Text version of the video https://csharp-video-tutorials.blogspot.co

YouTube

Mind Blowing Machines: This Is Engineering Heavy Equipment Impact Roller

This is the Engineering Heavy Equipment Impact Roller - Mind Blowing Machines BEST collection in the world -------------------------------- THANK YOU FOR WATCH

YouTube

SQL Server video :- Explain aggregate functions,group by clause and having keyword ?

For more such videos visit http://www.questpond.com For more such videos subscribe https://www.youtube.com/questpondvideos?sub_confirmation=1 See our other Step

YouTube

TCS SQL Interview Questions

Video talks about Top SQL Interview Questions Top SQL Interview Questions and answers sql faq sql interview questions qith answers Top 10 Frequently Asked Int

YouTube

Clustered and nonclustered indexes in sql server Part 36

In this video we will learn about 1. Different types of indexes in sql server 2. What are clustered indexes 3. What are NonClustered indexes 4. Difference betwe

YouTube

Part 3 Why and when should we use an abstract class

Link for code samples used in the demo http://csharp-video-tutorials.blogspot.com/2013/09/part-3-why-and-when-should-we-use.html Link for csharp, asp.net, ado.

YouTube

Part 6 Transform rows into columns in sql server

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

YouTube

SQL Server Interview Questions

SQL and T-SQL Interview Questions

YouTube

Part 8 Can an abstract class have a constructor

Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Can an abstract class have a constructor? If so wh

YouTube

Group by in sql server - Part 11

In this video we will learn 1. Grouping rows using GROUP BY 2. Filtering Groups 3. Difference between WHERE and HAVING clause in sql server Text version of the

YouTube

IQ15: 6 SQL Query Interview Questions

6 common SQL Query Questions

YouTube

SQL Server interview question :- Explain RowNumber,Partition,Rank and DenseRank ?

For more such videos visit http://www.questpond.com See our other Step by Step video series below :- Learn SQL Server Step by Step in 16 hours:- http://tinyurl

YouTube

What is SSIS , SSAS and SSRS ( part 1) with sample demo?

For more such videos visit http://www.questpond.com See our other Step by Step video series below :- Learn angular tutorial for beginners https://tinyurl.com/y

YouTube