Tags

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


SQL Puzzle | Fetch 2 records from each salary bracket

This is a good puzzle from interview stand point. In the puzzle you to break down salary data into below brackets & from each bracket return 2 records.

0-999
1000-1999
2000-2999
3000+

Please check out the sample input values and sample expected output below.

Sample Input

EmpId EmpName Salary
1 Pawan 1000
2 Ramesh 1100
3 Krishna 200
4 Sharlee 219
5 Isha 2000
6 Honey 576
7 Avika 7000
8 Mayank 8000
9 Pushpa 19000

Expected Output

EmpId EmpName Salary
3 Krishna 200
4 Sharlee 219
1 Pawan 1000
2 Ramesh 1100
5 Isha 2000
7 Avika 7000
8 Mayank 8000

Script

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

--


CREATE TABLE RangeEmployees
(

	 EmpId SMALLINT PRIMARY KEY
	,EmpName VARCHAR(10)
	,Salary INT
)
GO

INSERT INTO RangeEmployees VALUES
(1,'Pawan',1000),
(2,'Ramesh',1100),
(3,'Krishna',200),
(4,'Sharlee',219),
(5,'Isha',2000),
(6,'Honey',576),
(7,'Avika',7000),
(8,'Mayank',8000),
(9,'Pushpa',19000)
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 – 1


--

/*
**  Solution 1
*/

SELECT A.* FROM 
(VALUES (0,999), (1000,1999), (2000,2999), (3000,2147483647)) AS t(a,b)
CROSS APPLY
(
       SELECT TOP 2 EmpId,EmpName,Salary FROM rangeemployees R where r.salary BETWEEN t.a AND t.b
)A
GO

 

--

SOLUTION – 2


--

 
/*
**  Solution 2
*/

SELECT TOP 2 * 
       FROM RangeEmployees 
       WHERE Salary between 0 and 999 
UNION ALL
       SELECT TOP 2 * 
       FROM RangeEmployees
       WHERE Salary between 1000 and 1999
UNION ALL
       SELECT TOP 2 * 
       FROM RangeEmployees
       WHERE Salary between 2000 and 2999 
UNION ALL
       SELECT TOP 2 * 
       FROM RangeEmployees
       WHERE Salary >= 3000
 

--

SOLUTION – 3


--

/*
**  Solution 3
*/

SELECT EmpId,EmpName,Salary 
FROM 
(
       select *
       , row_number() over (partition by case 
                                                            when salary < 1000 then 1 
                                                            when salary < 2000 then 2 
                                                            when salary < 3000 then 3 
                                                            else 4 end 
                                          order by (select 1)) category
       from RangeEmployees
)A 
where category < 3
GO
 

--

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 Khowal 

Http://MSBISkills.com

Advertisements