Composite sql

[747576]

Hi,

This relates to thread thread183-1496513 which I am revisiting and need some more help with sql:

In the following code example I have a table of TEST id's that I want to composite into 3, so 1 composite is a combination of 3 test id's, this works fine. When I add the TYPE column to the group by it also works fine because in the table I have 3 PRIMARY id's then 3 TEST ids and so on....

but when I have 4 PRIMARY id's and then 3 PRIMARY id's (as in the table created below) it still groups the data and I end up with 2 records with the same ID value, this expected because TYPE is in the group by, but how would you modify the sql to have unique ID values in the resulting sql, in other terms the composite ID can never have both types 'PRIMARY' and 'TEST'.

Thank you

DROP TABLE NEWTABLE

CREATE TABLE NEWTABLE(ID VARCHAR(10), [FROM] NUMERIC(10), [TO] NUMERIC(10), [TYPE] VARCHAR(10))

INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST01','0','1','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST02','1','2','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST03','2','3.5','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST04','3.5','4','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST05','4','5','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST06','5','5.5','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST07','5.5','7','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST08','7','8','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST09','8','9','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST10','9','10','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST11','10','11','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST12','11','12','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST13','12','13.4','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST14','13.4','14','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST15','14','15','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST16','15','16','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST17','16','17','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST18','17','18','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST19','18','19','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST20','19','20','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST21','20','21','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST22','21','22','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST23','22','23','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST24','23','24','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST25','24','25','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST26','25','26','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST27','26','27','PRIMARY')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST28','27','28','TEST')
INSERT INTO NEWTABLE (ID,[FROM],[TO],[TYPE]) VALUES('TEST29','28','29','TEST')

--SELECT * FROM NEWTABLE



Select 'Comp' + Right('0' + Convert(varchar(10), 1 + (Rank-1)/3), 2) as [ID]
, Min([From]) as [From]
, Max([to]) as [To]
, Min([ID]) as [ID_FROM]
, Max([ID]) as [ID_TO]
, [TYPE]
From   (
       SELECT *, ROW_NUMBER() OVER(ORDER BY ID) As Rank
       FROM NEWTABLE
       ) As A
Group By (Rank - 1) / 3
,[TYPE]
order by [ID]