Daily Archives: October 31, 2015

SQL Puzzle | 2nd Highest Salary in Each Department using OffSet & Fetch Method

31 Saturday Oct 2015

Posted by in T SQL Puzzles

≈ Leave a comment

Tags

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


SQL Puzzle | 2nd Highest Salary in Each Department using OffSet & Fetch Method

Puzzle Statement

Recently I was doing something and one of my senior asked me how we can find department wise 2nd highest salary using Off Set and fetch method.

Sample Input

EmpId EmpName DepId EmpSalary
1 1 5
2 1 10
3 1 15
4 1 20
5 1 25
6 1 30
7 1 35
8 1 40
9 1 45
10
2 50
11 2 55
12 2 60
13
2 65
14 2 70
15 2 75
16 2 80
17 2 85
18 2 90
19 2 95
20 3 100
21 3 105
22 3 110
23 3 115
24 3 120
25 3 125
26 3 130
27 3 135
28 3 140
29 3 145
30 4 150
31 4 155
32 4 160
33 ! 4 165
34 ,4,170

Expected output

Depid SecondHighestSalary
1 40
2 90
3 140
4 190
5 240
6 290
7 340
8 390
9 440
10 490

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE EmployeeMaxSalary
(
     EmpId INT PRIMARY KEY
    ,EmpName VARCHAR(1)
	,DepId int
    ,EmpSalary INT 
)
GO
 
INSERT INTO EmployeeMaxSalary
SELECT DISTINCT Number , CHAR(Number) ,1+Number/10 ,Number * 10 / 2
FROM
    MASTER..SPT_VALUES
WHERE number > 0 AND number < 100
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION #

--


SELECT e.Depid, 
    ( SELECT p.EmpSalary
      FROM EmployeeMaxSalary p 
      WHERE p.depid = e.depid 
      ORDER BY p.EmpSalary DESC 
      OFFSET (1) ROWS FETCH NEXT 1 ROWS ONLY ) 
SecondHighestSalary
FROM EmployeeMaxSalary e
GROUP BY e.depid


--

Add a comment if you have any other solution in mind. I would love to learn it.

We all need to learn.

Enjoy !!! Keep Learning

Pawan Kumar Khowal 

Http://MSBISkills.com

SQL Puzzle | T-SQL Query Problem

31 Saturday Oct 2015

Posted by in T SQL Puzzles

≈ 3 Comments

Tags

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


SQL Puzzle | T-SQL Query Problem

Puzzle Statement

I go this question on Experts Exchange. In this puzzles you first have to get distinct rows from column3 and move the extra values from ColumnA to ColumnB. Check out the input and expected output required.

Sample Input

ColumnA ColumnB Column3
123 NULL 555
456 NULL 666
789 NULL 777
ABC NULL 555
DEF NULL 666
GHI NULL 777

Expected output

ColumnA ColumnB Column3
ABC 123 555
DEF 456 666
GHI 789 777

Script

Use below script to create table and insert sample data into it.

--


CREATE TABLE Column3
(
	 ColumnA VARCHAR(3)      
	,ColumnB VARCHAR(3)      
	,Column3 INT
)
GO

INSERT INTO Column3 VALUES
('123',NULL ,555),
('456',NULL ,666),
('789',NULL ,777),
('ABC',NULL ,555),
('DEF',NULL ,666),
('GHI',NULL ,777)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION #

--


;WITH CTE AS
(
	SELECT ColumnA , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnks1
	FROM Column3
),
CTE1 AS
(
	SELECT Column3 , COUNT(*) OVER() Cnts , 
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) r1
	FROM Column3
	GROUP BY Column3
)
,CTE2 AS
(
	SELECT ColumnA , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) r2
	FROM CTE WHERE rnks1 > (SELECT TOP 1 Cnts FROM CTE1 )
)
,CTE3 AS
(
	SELECT ColumnA ColumnB, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) r3
	FROM CTE WHERE rnks1 <= (SELECT TOP 1 Cnts FROM CTE1 )
)
SELECT CTE2.ColumnA , ColumnB , CTE1.Column3 FROM 
CTE1 INNER JOIN CTE2 on CTE1.r1 = CTE2.r2 INNER JOIN CTE3 ON CTE2.r2 = CTE3.r3


--

Add a comment if you have any other solution in mind. I would love to learn it.

We all need to learn.

Enjoy !!! Keep Learning

Pawan Kumar Khowal 

Http://MSBISkills.com