I don't understand how to add the grouped values on SQL-CodePudding

Data table:

| WINNER   | FOOT CLUB|
| -------- | --------   |
|     1    | Beşiktaş   |                 
|     2    | Beşiktaş   |                        
|     3    |Galatasaray |
|     4    |Galatasaray |
|     5    | Beşiktaş   |    
|     6    | Istanbul   |
|     7    | Istanbul   |
|     8    | Istanbul   |
|     9    |Galatasaray |
|    10    |Galatasaray |
|    11    |Fenerbahçe  |
|    12    |Fenerbahçe  |
|    13    |Fenerbahçe  |
|    14    | Istanbul   |

Help, please. I need to make a sorted array of a sequence of identical values appear. Use SQL syntax of any version. I need this result:

Beşiktaş    2
Galatasaray 2
Beşiktaş    1
Istanbul    3
Galatasaray 2
Fenerbahçe  3
Istanbul    1

CREATE TABLE football (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
   );

INSERT INTO football VALUES (1, 'Beşiktaş');
INSERT INTO football VALUES (2, 'Beşiktaş');
INSERT INTO football VALUES (3, 'Galatasaray');
INSERT INTO football VALUES (4, 'Galatasaray');
INSERT INTO football VALUES (5, 'Beşiktaş');
INSERT INTO football VALUES (6, 'Istanbul');
INSERT INTO football VALUES (7, 'Istanbul');
INSERT INTO football VALUES (8, 'Istanbul');
INSERT INTO football VALUES (9, 'Galatasaray');
INSERT INTO football VALUES (10, 'Galatasaray');
INSERT INTO football VALUES (11, 'Fenerbahçe');
INSERT INTO football VALUES (12, 'Fenerbahçe');
INSERT INTO football VALUES (13, 'Fenerbahçe');
INSERT INTO football VALUES (14, 'Istanbul');

SELECT name, 
RANK() OVER() 
FROM football

it turned out like this:

Beşiktaş|1
Beşiktaş|1
Galatasaray|1
Galatasaray|1
Beşiktaş|1
Istanbul|1
Istanbul|1
Istanbul|1
Galatasaray|1
Galatasaray|1
Fenerbahçe|1
Fenerbahçe|1
Fenerbahçe|1
Istanbul|1

CodePudding user response：

The below was adapted from this solution.

Dbfiddle for your solution if desired

select name, count(*) as cnt
from (select t.*,
             (row_number() over (order by id) - row_number() over (partition by name order by id)
             ) as grp
      from football t
     ) t
group by name, grp
order by min(id) asc