Grouping similar rows

[jan_so...@yahoo.se]

Hi

I would like to group rows based on order and similarity in a single
sql-query if possible:

A, 1, 50
A, 2, 50
A, 3, 100
A, 4, 50
A, 5, 100
A, 6, 100

Would come out as:

A, 1, 50, 2 <-- Last value shows number of grouped rows
A, 2, 100, 1
A, 3, 50, 1
A, 4, 100, 2

Any suggestions?

[Hugo Kornelis]

Hi Jan,

I believe that this will do what you need (SQL Server 2005 only):

CREATE TABLE TheInput
(Letter char(1) NOT NULL,
RowNum int NOT NULL,
OtherValue int NOT NULL,
PRIMARY KEY (Letter, RowNum));
go
-- Original testset
INSERT INTO TheInput (Letter, RowNum, OtherValue)
SELECT 'A', 1, 50
UNION ALL
SELECT 'A', 2, 50
UNION ALL
SELECT 'A', 3, 100
UNION ALL
SELECT 'A', 4, 50
UNION ALL
SELECT 'A', 5, 100
UNION ALL
SELECT 'A', 6, 100;
-- Extra testcases
INSERT INTO TheInput (Letter, RowNum, OtherValue)
SELECT 'B', 1, 50
UNION ALL
SELECT 'B', 2, 100
UNION ALL
SELECT 'B', 3, 50;
go
WITH CTE AS
(SELECT Letter, OtherValue,
ROW_NUMBER() OVER (PARTITION BY Letter
ORDER BY RowNum) AS rn1,
ROW_NUMBER() OVER (PARTITION BY Letter, OtherValue
ORDER BY RowNum) AS rn2
FROM TheInput)
SELECT Letter,
ROW_NUMBER() OVER (PARTITION BY Letter
ORDER BY MIN(rn1)) AS NewRowNumber,
OtherValue,
COUNT(*) AS NumberOfGroupedRows
FROM CTE
GROUP BY Letter, OtherValue, (rn1-rn2)
ORDER BY Letter, MIN(rn1);
go
DROP TABLE TheInput;
go

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis