SQL Puzzle | The Maximum Repetition Puzzle

Ok, This is a very simple but interesting interview puzzle. You have a list of numbers and you have to find out maximum repeating number. In below case number 5 is the most repeating number.

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 Maxrepetition
(
	 ID SMALLINT
	,Val INT
)
GO

INSERT INTO Maxrepetition
VALUES
(1,8),
(2,8),
(3,3),
(4,4),
(5,5),
(6,4),
(7,5),
(8,5)
GO

CREATE UNIQUE CLUSTERED INDEX Ix_ID ON Maxrepetition(ID)
GO

CREATE NONCLUSTERED INDEX Ix_Val ON Maxrepetition(Val)
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 Group By

--

SELECT ( SELECT TOP 1 Id FROM Maxrepetition t WHERE z.Val = t.Val ) Id , z.* FROM 
(
	SELECT TOP 1 Val , COUNT(*) Cnt
	FROM Maxrepetition s
	GROUP BY Val
	ORDER BY Cnt DESC
)z

--

SOLUTION – 2 | Using Ranking Function

--

SELECT TOP 1 Id,Val,rnk Cnt FROM 
(
	SELECT Id,Val,COUNT(*) OVER ( PARTITION BY Val ORDER BY Val ) rnk
	FROM Maxrepetition
)r 
ORDER BY rnk DESC


--

SOLUTION – 3 | Using CROSS APPLY

--

SELECT TOP 1 s.Id, s.Val,t.Cnt 
FROM Maxrepetition s
CROSS APPLY
(
	SELECT COUNT(*) Cnt FROM Maxrepetition r WHERE r.Val = s.Val
)t
ORDER BY t.Cnt DESC

--

–

Execution plans for above queries-

Execution Plans for Maximum Repetition

–

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