Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (ord, name) AS
VALUES
  ( 1, 'A')
, ( 2, 'A')
, ( 3, 'A')
, ( 4, 'B')
, ( 5, 'B')
, ( 6, 'A')
, ( 7, 'A')
, ( 8, 'C')
, ( 9, 'C')
, (10, 'B')
, (11, 'B')
, (12, 'B')
;

SELECT 12


-- 0-based
SELECT ord, name
     , count(step or NULL) OVER (ORDER BY ord) AS myrank
FROM  (
   SELECT ord, name, name <> lag(name) OVER (ORDER BY ord) AS step
   FROM   tbl
   ) sub;


ord
name
myrank


1
A
0

2
A
0

3
A
0

4
B
1

5
B
1

6
A
2

7
A
2

8
C
3

9
C
3

10
B
4

11
B
4

12
B
4



SELECT 12


-- 1-based
SELECT ord, name
     , count(step or NULL) OVER (ORDER BY ord) + 1 AS myrank
FROM  (
   SELECT ord, name, name <> lag(name) OVER (ORDER BY ord) AS step
   FROM   tbl
   ) sub;


ord
name
myrank


1
A
1

2
A
1

3
A
1

4
B
2

5
B
2

6
A
3

7
A
3

8
C
4

9
C
4

10
B
5

11
B
5

12
B
5



SELECT 12


-- Optimized for Postgres
SELECT ord, name
     , count(*) FILTER (WHERE step) OVER (ORDER BY ord) + 1 AS myrank
FROM  (
   SELECT *, name <> lag(name) OVER (ORDER BY ord) AS step
   FROM   tbl
   ) sub;


ord
name
myrank


1
A
1

2
A
1

3
A
1

4
B
2

5
B
2

6
A
3

7
A
3

8
C
4

9
C
4

10
B
5

11
B
5

12
B
5



SELECT 12