SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(

Id INTEGER NOT NULL PRIMARY KEY

,GroupId INTEGER NOT NULL

,RouteId VARCHAR(14) NOT NULL

,LengthStart NUMERIC(9,7) NOT NULL

,LengthEnd NUMERIC(9,7) NOT NULL

,LocationId INTEGER NOT NULL

);

INSERT INTO mytable VALUES (2651246,3,'AAA',0,0.0920000,1884268)

,(2651247,3,'AAA',0.0920000,0.5800000,1855305)

,(2651248,3,'AAA',0.5800000,1.3610000,1884268)

,(2651249,3,'AAA',1.3610000,1.6170000,1884268)

,(2651250,3,'AAA',1.6170000,2.3750000,1884268)

,(2681493,3,'BBB',0.0000000,1.5600000,1864963)

,(2681494,3,'BBB',1.5600000,2.7100000,1864963)

,(2681495,3,'BBB',2.7100000,3.3900000,1864963)

,(2954915,3,'CCC',0.0000000,0.0500000,1883382)

,(2954916,3,'CCC',0.0500000,0.1400000,1846300)

,(2954917,3,'CCC',0.1400000,0.2800000,1846300)

,(2954918,3,'CCC',0.2800000,0.3900000,1856832)

,(2954919,3,'CCC',0.3900000,0.5800000,1856832)

,(2954920,3,'CCC',0.5800000,0.9100000,1856546)

,(2954921,3,'CCC',0.9100000,0.9900000,1883385)

,(2954922,3,'CCC',0.9900000,1.1130000,1883385)

,(2954923,3,'CCC',1.1130000,1.4400000,1856548)

,(2954924,3,'CCC',1.4400000,1.5000000,1856548)

,(2954925,3,'CCC',1.5000000,1.5300000,1856548)

,(2954926,3,'CCC',1.5300000,1.6200000,1856548)

,(2954927,3,'CCC',1.6200000,1.6400000,1856548)

,(2954928,3,'CCC',1.6400000,2.0240000,1856224)

,(2954929,3,'CCC',2.0240000,2.4200000,1856754)

,(2954930,3,'CCC',2.4200000,3.6600000,1855576)

,(3160474,4,'AAA',0.0000000,0.0920000,1884268)

,(3160475,4,'AAA',0.0920000,0.5800000,1855305)

,(3160476,4,'AAA',0.5800000,1.3610000,1884268)

96 rows affected

SELECT *, ROW_NUMBER() OVER (ORDER BY GroupId, RouteId, LengthStart) - ROW_NUMBER() OVER (PARTITION BY GroupId, RouteId, LocationId ORDER BY GroupId, RouteId, LengthStart) AS grp

FROM mytable

ORDER BY GroupId, RouteId, LengthStart

Id	GroupId	RouteId	LengthStart	LengthEnd	LocationId	grp
2651246	3	AAA	0.0000000	0.0920000	1884268	0
2651247	3	AAA	0.0920000	0.5800000	1855305	1
2651248	3	AAA	0.5800000	1.3610000	1884268	1
2651249	3	AAA	1.3610000	1.6170000	1884268	1
2651250	3	AAA	1.6170000	2.3750000	1884268	1
2681493	3	BBB	0.0000000	1.5600000	1864963	5
2681494	3	BBB	1.5600000	2.7100000	1864963	5
2681495	3	BBB	2.7100000	3.3900000	1864963	5
2954915	3	CCC	0.0000000	0.0500000	1883382	8
2954916	3	CCC	0.0500000	0.1400000	1846300	9
2954917	3	CCC	0.1400000	0.2800000	1846300	9
2954918	3	CCC	0.2800000	0.3900000	1856832	11
2954919	3	CCC	0.3900000	0.5800000	1856832	11
2954920	3	CCC	0.5800000	0.9100000	1856546	13
2954921	3	CCC	0.9100000	0.9900000	1883385	14
2954922	3	CCC	0.9900000	1.1130000	1883385	14
2954923	3	CCC	1.1130000	1.4400000	1856548	16
2954924	3	CCC	1.4400000	1.5000000	1856548	16
2954925	3	CCC	1.5000000	1.5300000	1856548	16
2954926	3	CCC	1.5300000	1.6200000	1856548	16
2954927	3	CCC	1.6200000	1.6400000	1856548	16
2954928	3	CCC	1.6400000	2.0240000	1856224	21
2954929	3	CCC	2.0240000	2.4200000	1856754	22
2954930	3	CCC	2.4200000	3.6600000	1855576	23
3160474	4	AAA	0.0000000	0.0920000	1884268	24
3160475	4	AAA	0.0920000	0.5800000	1855305	25
3160476	4	AAA	0.5800000	1.3610000	1884268	25
3160477	4	AAA	1.3610000	1.6170000	1884268	25
3160478	4	AAA	1.6170000	2.3750000	1884268	25
3194295	4	BBB	0.0000000	1.5600000	1864963	29
3194296	4	BBB	1.5600000	1.7800000	1864963	29
3194297	4	BBB	1.7800000	2.7100000	1864963	29
3194298	4	BBB	2.7100000	3.3900000	1864963	29
3459348	4	CCC	0.0000000	0.0500000	1883382	33
3459349	4	CCC	0.0500000	0.1400000	1846300	34
3459350	4	CCC	0.1400000	0.2800000	1846300	34
3459351	4	CCC	0.2800000	0.3200000	1856832	36
3459352	4	CCC	0.3200000	0.3900000	1856832	36
3459353	4	CCC	0.3900000	0.4800000	1856832	36
3459354	4	CCC	0.4800000	0.5800000	1856832	36
3459355	4	CCC	0.5800000	0.9100000	1856546	40
3459356	4	CCC	0.9100000	0.9900000	1883385	41
3459357	4	CCC	0.9900000	1.1130000	1883385	41
3459358	4	CCC	1.1130000	1.4400000	1856548	43
3459359	4	CCC	1.4400000	1.5000000	1856548	43
3459360	4	CCC	1.5000000	1.5300000	1856548	43
3459361	4	CCC	1.5300000	1.6200000	1856548	43
3459362	4	CCC	1.6200000	1.6400000	1856548	43
3459363	4	CCC	1.6400000	2.0240000	1856224	48
3459364	4	CCC	2.0240000	2.4200000	1856754	49
3459365	4	CCC	2.4200000	3.1900000	1855576	50
3673423	5	AAA	0.0000000	0.0920000	1884268	51
3673424	5	AAA	0.0920000	0.5800000	1855305	52
3673425	5	AAA	0.5800000	2.3750000	1884268	52
3690129	5	BBB	0.0000000	1.7800000	1864963	54
3690130	5	BBB	1.7800000	2.7100000	1864963	54
3690131	5	BBB	2.7100000	4.2700000	1864963	54
3809208	5	CCC	0.0000000	0.0500000	1883382	57
3809209	5	CCC	0.0500000	0.2800000	1846300	58
3809210	5	CCC	0.2800000	0.3200000	1856832	59
3809211	5	CCC	0.3200000	0.3900000	1856832	59
3809212	5	CCC	0.3900000	0.4800000	1856832	59
3809213	5	CCC	0.4800000	0.5800000	1856832	59
3809214	5	CCC	0.5800000	0.9100000	1856546	63
3809215	5	CCC	0.9100000	1.1130000	1883385	64
3809216	5	CCC	1.1130000	1.6400000	1856548	65
3809217	5	CCC	1.6400000	2.0240000	1856224	66
3809218	5	CCC	2.0240000	2.4200000	1856754	67
3809219	5	CCC	2.4200000	3.1900000	1855576	68
5612766	33	AAA	0.0000000	0.0920000	857561291	69
5612767	33	AAA	0.0920000	0.5800000	857563282	70
5612768	33	AAA	0.5800000	1.3610000	857561291	70
5612769	33	AAA	1.3610000	1.6170000	857561291	70
5612770	33	AAA	1.6170000	2.3750000	857561291	70
5646587	33	BBB	0.0000000	1.5600000	857572170	74
5646588	33	BBB	1.5600000	1.7800000	857572170	74
5646589	33	BBB	1.7800000	2.7100000	857572170	74
5646590	33	BBB	2.7100000	3.3900000	857572170	74
5911642	33	CCC	0.0000000	0.0500000	857578871	78
5911643	33	CCC	0.0500000	0.1400000	857570960	79
5911644	33	CCC	0.1400000	0.2800000	857570960	79
5911645	33	CCC	0.2800000	0.3200000	857578972	81
5911646	33	CCC	0.3200000	0.3900000	857578972	81
5911647	33	CCC	0.3900000	0.4800000	857578972	81
5911648	33	CCC	0.4800000	0.5800000	857578972	81
5911649	33	CCC	0.5800000	0.9100000	857579076	85
5911650	33	CCC	0.9100000	0.9900000	857579169	86
5911651	33	CCC	0.9900000	1.1130000	857579169	86
5911652	33	CCC	1.1130000	1.4400000	857579256	88
5911653	33	CCC	1.4400000	1.5000000	857579256	88
5911654	33	CCC	1.5000000	1.5300000	857579256	88
5911655	33	CCC	1.5300000	1.6200000	857579256	88
5911656	33	CCC	1.6200000	1.6400000	857579256	88
5911657	33	CCC	1.6400000	2.0240000	857579329	93
5911658	33	CCC	2.0240000	2.4200000	857579412	94
5911659	33	CCC	2.4200000	3.1900000	857579516	95