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”.

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