Postgres 15

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

CREATE TABLE mytable (
   id int,
   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


with cte as (
  SELECT
    *, row_number() over(order by id) - row_number() over (partition by name order by id) as grp
  FROM mytable
)
select *
from cte


id
name
grp


1
car
0

2
car
0

3
bike
2

4
bike
2

5
truck
4

6
car
3

7
car
3

8
car
3

9
car
3

10
bike
7

11
bike
7

12
truck
10

13
truck
10



SELECT 13


with cte as (
  SELECT
    *, row_number() over(order by id) - row_number() over (partition by name order by id) as grp
  FROM mytable
)
select name, count(1)
from cte
group by name, grp
order by count(1) 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