Tags
Cardinality, Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, msbi skills, MSBISkills, msbiskills.com, Second highest salary puzzle
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
Improving my SQL BI Skills 
SELECT Name,SALARY FROM (
SELECT Name,Salary,ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rnk FROM NthHighest) R
WHERE R.Rnk =2
LikeLike
SELECT name, salary
FROM NthHightest
GROUP BY salary DESC
LIMIT 1, 1;
LikeLike
SELECT TOP 1 *
FROM
(
SELECT TOP 2 Name,Salary
FROM NthHighest
ORDER BY Salary DESC
)dt
ORDER BY Salary;
LikeLiked by 1 person