Min, max groups

1

Have a table like this
Group invoice#
A 1
A 2
A 4
A 5
A 6
B 101
B 102
B 103
B 105
B 106
I need the result:
Group, min, max
A 1 2
A 4 6
B 101 103
B 105 106
Sql should be optimized for a large table.

Thank you

2

what have you tried? (post your query).

Also I don't get the sample output. How do you decide to split up the groups that way?

3

Please post consumable test data in future:

CREATE TABLE #t
(
	IGroup char(1) NOT NULL
	,InvoiceNo int NOT NULL
);
INSERT INTO #t
VALUES ('A',1),('A',2)
	,('A',4),('A',5),('A',6)
	,('B',101),('B',102),('B',103)
	,('B',105),('B',106);

Try something like:

WITH Grps
AS
(
	SELECT IGroup, InvoiceNo
		,InvoiceNo - ROW_NUMBER() OVER (PARTITION BY IGroup ORDER BY InvoiceNo) AS Grp
	FROM #t
)
,Results
AS
(
	SELECT IGroup, Grp, MIN(InvoiceNo) AS MinInvoiceNo, MAX(InvoiceNo) AS MaxInvoiceNo
	FROM Grps
	GROUP BY IGroup, Grp
)
SELECT IGroup, MinInvoiceNo, MaxInvoiceNo
FROM Results
ORDER BY IGroup, MinInvoiceNo;
1 Like
4

Thank you very much, Ifor. Was perfect.