SQL Server 2014

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

CREATE TABLE Test
          (
              id int,
              Type VARCHAR(10),
              SectionId int
          )
Insert into Test
SELECT 1,'RED', 100
UNION ALL SELECT 2, 'RED', 100
UNION ALL  SELECT 3, 'BLUE', 100
UNION ALL SELECT  4, 'RED', 200
UNION ALL SELECT  5, 'BLUE', 200
UNION ALL SELECT  6, 'RED', 300
UNION ALL SELECT  7, 'BLUE', 300
UNION ALL SELECT  8, 'BLUE', 300
8 rows affected

SELECT id, type, sectionid
     , dense_rank() OVER (PARTITION BY sectionid ORDER BY type DESC) AS RowNumber
FROM test
ORDER BY id;


id
type
sectionid
RowNumber


1
RED
100
1

2
RED
100
1

3
BLUE
100
2

4
RED
200
1

5
BLUE
200
2

6
RED
300
1

7
BLUE
300
2

8
BLUE
300
2

id	type	sectionid	RowNumber
1	RED	100	1
2	RED	100	1
3	BLUE	100	2
4	RED	200	1
5	BLUE	200	2
6	RED	300	1
7	BLUE	300	2
8	BLUE	300	2