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

Advertisements