MySQL Database Tutorial - 17

MySQL Database Tutorial - 17 - Functions

Январь 18, 2012

Facebook — https://www.facebook.com/TheNewBoston-464114846956315/
GitHub — https://github.com/buckyroberts
Google — https://plus.google.com/ BuckyRoberts
LinkedIn — https://www.linkedin.com/in/buckyroberts
reddit — https://www.reddit.com/r/thenewboston/
Support — https://www.patreon.com/thenewboston
thenewboston — https://thenewboston.com/
Twitter — https://twitter.com/bucky_roberts

663 8

132,000 просмотров

comments powered by Disqus

7:57

MySQL Database Tutorial - 18 - More on Aggregate Functions

627 4

105,785 просмотров

11:45

Part 1 How to find nth highest salary 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 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

4529 149

1,016,357 просмотров

9:35

DDL, DML, DCL & TCL statements in SQL (Database basics)

DDL, DML, DCL & TCL statements in SQL (Database basics)

Check my blog on this: https://sqlwithmanoj.com/2009/02/10/what-are-ddl-dml-dcl-and-tcl-commands-difference-bw-them/

Check the whole "SQL Server Basics" series here: https://www.youtube.com/playlist?list=PLU9JMEzjCv14f3cWDhubPaddxRvx1reKR

Check my SQL blog at: http://sqlwithmanoj.com/

Check my SQL FB Page at: https://www.facebook.com/sqlwithmanoj

2883 218

345,654 просмотров

20:2

MySQL Stored Procedures and Functions (Topic #5: MySQL course from JPassion.com)

This is a partial presentation. For complete presentation, hands-on lab document, and sample apps, please go to http://jpassion.com and acquire full subscription.

249 16

77,382 просмотров

12:32