SQL Puzzle | Break Table in Equal Parts

In this puzzle you have to break the table in equal parts. Let’s say you have 14 rows in the table and the ask was to break the table in 3 equal parts – of course in the first 2 parts we will have 5 rows and in the last part we will have 4 rows.

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

Sample Input

Expected Output

Script

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

--


CREATE TABLE EmpRangers
(
	 EmpId INT
	,EmpName VARCHAR(10)
	,Salary INT
)
GO

INSERT INTO EmpRangers
VALUES
(1,'a',1000),
(2,'b',900),
(3,'c',100),
(4,'d',1100),
(5,'e',1300),
(6,'f',700),
(7,'g',330),
(8,'h',800),
(9,'i',500),
(10,'j',340),
(11,'k',600),
(12,'l',700),
(13,'m',1000)
GO

INSERT INTO EmpRangers
VALUES
(14,'n',1800)
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 , Using Ranking NTILE Function

--

;WITH CTE AS 
(
	SELECT 
		     Salary
		    ,NTILE(3) OVER (ORDER BY Salary) Grouper
	FROM 
		    EmpRangers
)
SELECT Grouper, CONCAT(MIN(Salary),'-',MAX(Salary)) EmpSalaryRanges, COUNT(*) Counts
FROM CTE
GROUP BY Grouper

--

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