Tags

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


T-SQL Query | [ Find 2nd highest Salary using Top Clause ]

The puzzle is very simple. Here you have to find 2nd highest salary details of employees using Top clause. Please note that Top clause is a must in this case and if there are multiple values for 2nd highest details then the query should return all the duplicate values. Please check out the sample input and expected output for details.

Sample Input

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

Expected output

Name Salary
E10 49000
E11 49000
e2 49000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Top clause is a must in this case.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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),
('E10',49000),
('E11',49000)

--Check data
SELECT Name,Salary FROM NthHighest
--
 

UPDATE – 10-Apr-2015 – Solution 1


-- Solution 1 --

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

--

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

Keep Learning

http://MSBISkills.com

Advertisements