Daily Archives: August 3, 2010

SQL Server Query – 6 | Nth Highest Salary

03 Tuesday Aug 2010

Posted by in T SQL Puzzles

≈ 3 Comments

Tags

, , , , , , , , , , , , , , , , , ,


T-SQL Query | [Nth Highest Salary Puzzle] – Write a query which will find the Nth highest salary from the table. In this case we are finding 2nd Highest Salary

Sample Input

Name Salary
e5 45000
e3 30000
e2 49000
e4 36600
e1 58000

Expected Output

Name Salary
e2 49000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create table

CREATE TABLE NthHighest
(
 Name  varchar(5)  NOT NULL,
 Salary  int  NOT NULL
)

--Insert the values
INSERT INTO  NthHighest(Name, Salary)
VALUES 
('e5', 45000),
('e3', 30000),
('e2', 49000),
('e4', 36600),
('e1', 58000)

--Check data
SELECT Name,Salary FROM NthHighest

Update May 14 | Solution


--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------

SELECT * FROM NthHighest N WHERE 1 = (SELECT DISTINCT COUNT(*) FROM NthHighest N1 WHERE N1.Salary > N.Salary )

---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------

SELECT TOP 1 WITH TIES Name , Salary FROM NthHighest N1 
WHERE Salary IN (SELECT TOP 2 WITH TIES Salary FROM NthHighest ORDER BY Salary DESC)
ORDER BY Salary

---------------------------------------
--Sol 3 | Pawan Kumar Khowal
---------------------------------------

SELECT A.Name, B.Salary
FROM (
	SELECT MAX(Salary) Salary
	FROM NthHighest N1 
	WHERE N1.Salary != (SELECT MAX(Salary) FROM NthHighest) ) B
CROSS APPLY (SELECT NAME FROM NthHighest WHERE SALARY = B.SALARY ) A

---------------------------------------
--Sol 4 | Pawan Kumar Khowal
---------------------------------------

;WITH CTE AS
(
	SELECT * , RANK() OVER (ORDER BY SALARY DESC) rnk FROM NthHighest
)
SELECT Name, Salary FROM CTE WHERE rnk = 2


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Nth Highest Salary in SQL SERVER 2005 – Generic Logic

03 Tuesday Aug 2010

Posted by in SQL Concepts, T SQL Puzzles

≈ Leave a comment

Tags

, , , , , ,


– Generic Logic To Find Highest Salary
– Query can be used to find out n’th salary, set @rowNum = n where n is an integer


– Create a table
CREATE TABLE Employee
(

Name varchar(50) NOT NULL,
Salary int NOT NULL

)

– Insert the values
INSERT INTO Employee(Name, Salary)
VALUES (‘e5′, 45000)

INSERT INTO Employee(Name, Salary)
VALUES (‘e3′,  30000)

INSERT INTO Employee( Name, Salary )
VALUES (‘e2′,  49000)

INSERT INTO Employee( Name, Salary )
VALUES (‘e4′,  36600)

INSERT INTO Employee( Name, Salary )
VALUES (‘e1′  ,  58000)

SELECT * FROM Employee

– Select second highest salary
DECLARE @rowNum int
SET @rowNum = 2

Select top 1 * from Employee
WHERE salary in (SELECT TOP (@rowNum) salary FROM Employee ORDER BY salary DESC) ORDER BY salary

–Gauri Deshpande and Pawan Kumar
pawankkmr@hotmail.com