Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQLSERVER, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
select top(1) with ties name, salary
from (select top(2) with ties * from NthHighest order by salary desc) tbl
order by salary
LikeLike
SELECT n.Name,n.Salary FROM
(
SELECT TOP 1 * FROM
(
SELECT TOP 2 * FROM NthHighest ORDER BY Salary DESC
)tbl1 ORDER BY Salary
)tabl2
inner join
NthHighest n on tabl2.Salary=n.Salary
LikeLike
;with cte as (
SELECT Name,Salary, dense_rank() over (order by salary desc) as dr FROM NthHighest)
select name, salary from cte where dr=2
LikeLike