Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
   id numeric,
   name text
);

INSERT INTO mytable VALUES 
  (1, 'car'),
  (2, 'car'),
  (3, 'bike'),
  (4, 'bike'),
  (5, 'truck'),
  (6, 'car'),
  (7, 'car'),
  (8, 'car'),
  (9, 'car'),
  (10, 'bike'),
  (11, 'bike'),
  (12, 'truck'),
  (13, 'truck');

SELECT * FROM mytable

CREATE TABLE



INSERT 0 13




id
name


1
car

2
car

3
bike

4
bike

5
truck

6
car

7
car

8
car

9
car

10
bike

11
bike

12
truck

13
truck



SELECT 13


SELECT
    *,
    lag(name) OVER (ORDER BY id)
FROM mytable


id
name
lag


1
car
null

2
car
car

3
bike
car

4
bike
bike

5
truck
bike

6
car
truck

7
car
car

8
car
car

9
car
car

10
bike
car

11
bike
bike

12
truck
bike

13
truck
truck



SELECT 13


SELECT
    *,
    name != lag(name) OVER (ORDER BY id)
FROM mytable


id
name
?column?


1
car
null

2
car
f

3
bike
t

4
bike
f

5
truck
t

6
car
t

7
car
f

8
car
f

9
car
f

10
bike
t

11
bike
f

12
truck
t

13
truck
f



SELECT 13


SELECT
    *,
    (name != lag(name) OVER (ORDER BY id))::int as eq
FROM mytable


id
name
eq


1
car
null

2
car
0

3
bike
1

4
bike
0

5
truck
1

6
car
1

7
car
0

8
car
0

9
car
0

10
bike
1

11
bike
0

12
truck
1

13
truck
0



SELECT 13


SELECT
    *,
    SUM(eq) OVER (ORDER BY id) as group_id
FROM (
    SELECT
        *,
        (name != lag(name) OVER (ORDER BY id))::int as eq
    FROM mytable
) s


id
name
eq
group_id


1
car
null
null

2
car
0
0

3
bike
1
1

4
bike
0
1

5
truck
1
2

6
car
1
3

7
car
0
3

8
car
0
3

9
car
0
3

10
bike
1
4

11
bike
0
4

12
truck
1
5

13
truck
0
5



SELECT 13


SELECT
    *,
    COUNT(*) OVER (PARTITION BY group_id)
FROM (
    SELECT
        *,
        SUM(eq) OVER (ORDER BY id) as group_id
    FROM (
        SELECT
            *,
            (name != lag(name) OVER (ORDER BY id))::int as eq
        FROM mytable
    ) s
) s


id
name
eq
group_id
count


2
car
0
0
1

3
bike
1
1
2

4
bike
0
1
2

5
truck
1
2
1

8
car
0
3
4

6
car
1
3
4

7
car
0
3
4

9
car
0
3
4

10
bike
1
4
2

11
bike
0
4
2

13
truck
0
5
2

12
truck
1
5
2

1
car
null
null
1



SELECT 13


SELECT
    *,
    COUNT(*) OVER (PARTITION BY group_id)
FROM (
    SELECT
        *,
        SUM(eq) OVER (ORDER BY id) as group_id
    FROM (
        SELECT
            *,
            (name != lag(name) OVER (ORDER BY id))::int as eq
        FROM mytable
    ) s
) s
ORDER BY count DESC


id
name
eq
group_id
count


7
car
0
3
4

9
car
0
3
4

8
car
0
3
4

6
car
1
3
4

10
bike
1
4
2

13
truck
0
5
2

11
bike
0
4
2

12
truck
1
5
2

4
bike
0
1
2

3
bike
1
1
2

1
car
null
null
1

5
truck
1
2
1

2
car
0
0
1



SELECT 13


SELECT
    name,
    COUNT(*) OVER (PARTITION BY group_id)
FROM (
    SELECT
        *,
        SUM(eq) OVER (ORDER BY id) as group_id
    FROM (
        SELECT
            *,
            (name != lag(name) OVER (ORDER BY id))::int as eq
        FROM mytable
    ) s
) s
ORDER BY count DESC
LIMIT 1


name
count


car
4



SELECT 1
id	name
1	car
2	car
3	bike
4	bike
5	truck
6	car
7	car
8	car
9	car
10	bike
11	bike
12	truck
13	truck
id	name	lag
1	car	null
2	car	car
3	bike	car
4	bike	bike
5	truck	bike
6	car	truck
7	car	car
8	car	car
9	car	car
10	bike	car
11	bike	bike
12	truck	bike
13	truck	truck