Problem Statement :

Given below table Emp as Input which has two columns 'Group' and 'Sequence'. Write a SQL query to find the maximum and minimum values of continuous ‘Sequence’ in each ‘Group’

INPUT:


OUTPUT:




Table and Insert SQL Script : CREATE TABLE Emp( [Group] varchar(20), [Sequence] int ) INSERT INTO Emp VALUES('A',1) INSERT INTO Emp VALUES('A',2) INSERT INTO Emp VALUES('A',3) INSERT INTO Emp VALUES('A',5) INSERT INTO Emp VALUES('A',6) INSERT INTO Emp VALUES('A',8) INSERT INTO Emp VALUES('A',9) INSERT INTO Emp VALUES('B',11) INSERT INTO Emp VALUES('C',1) INSERT INTO Emp VALUES('C',2) INSERT INTO Emp VALUES('C',3)


Solution 1:

; WITH DetailData as( SELECT [Group], [Sequence], ROW_NUMBER() OVER(PARTITION BY [GROUP] ORDER BY [Sequence]) as RN, [Sequence]- ROW_NUMBER() OVER(PARTITION BY [GROUP] ORDER BY [Sequence]) as Group_Split FROM Emp ) SELECT [Group], MIN([Sequence]) as Min_Sequence, MAX([Sequence]) as Max_Sequence FROM DetailData GROUP BY [Group], Group_Split ORDER BY [Group];

Solution 2:

SELECT [Group], MIN([Sequence]) as Min_Sequence, MAX([Sequence]) as Max_Sequence FROM ( SELECT [Group], [Sequence], ROW_NUMBER() OVER(PARTITION BY [GROUP] ORDER BY [Sequence]) as RN, [Sequence]- ROW_NUMBER() OVER(PARTITION BY [GROUP] ORDER BY [Sequence]) as Group_Split FROM Emp ) as A GROUP BY [Group], Group_Split ORDER BY [Group];