Mysql - Count rows until a first distinct value is reached

Question

I am trying to count the number of rows until the first occurrence of distinct value, for every distinct value.

Table example

game winner
-----------
1    Mark
2    Joe
3    Mark
4    Paula
5    Paula
6    Paula
7    Joe
8    Anna

With query below i get this..

SELECT winner,COUNT(*) as count FROM tablename GROUP BY winner;

Result

Mark won 2 games
Joe won 2 games
Paula won 3 games
Anna won 1 game

Below are the results that I want to get:

Mark won 2 games, but didn't won last 6 games 
Joe won 2 games, but didn't won last 1 games 
Paula won 3 games, but didn't won last 2 games 
Anna won 1 game, but didn't won last 0 games

Thank you for taking the time to help me, I really appreciate it.

By the way, this is *last 5 games* for Mark – Cid Feb 19 '19 at 14:17 — Cid, Feb 19 '19 at 14:17

score 1 · Accepted Answer · edited Feb 19 '19 at 14:06

1

You can count the last not won games in a correlated subquery.

select winner, count(*) as won, (
  select count(*)
  from tablename t2
  where t2.game > max(t1.game)
) as not_won
from tablename t1
group by winner

Demo: https://www.db-fiddle.com/f/czHPqscvEGgLPLeVYHV5hk/0

edited Feb 19 '19 at 14:06

Strawberry

33,750
13
40
57

answered Feb 19 '19 at 13:59

Paul Spiegel

30,925
5
44
53

score 1 · Answer 2 · answered Feb 19 '19 at 14:03

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(game SERIAL PRIMARY KEY
,winner VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1,'Mark'),
(2,'Joe'),
(3,'Mark'),
(4,'Paula'),
(5,'Paula'),
(6,'Paula'),
(7,'Joe'),
(8,'Anna');

SELECT x.winner 
     , y.total
     , COUNT(z.game) games_since_last_win
  FROM my_table x 
  JOIN 
     ( SELECT COUNT(game) total
            , MAX(game) game 
         FROM my_table 
        GROUP 
           BY winner
     ) y 
    ON y.game = x.game
  LEFT
  JOIN my_table z
    ON z.winner <> x.winner
   AND z.game > x.game
 GROUP   
    BY x.winner;
+--------+-------+----------------------+
| winner | total | games_since_last_win |
+--------+-------+----------------------+
| Anna   |     1 |                    0 |
| Joe    |     2 |                    1 |
| Mark   |     2 |                    5 |
| Paula  |     3 |                    2 |
+--------+-------+----------------------+

Cid · Answer 3 · 2019-02-19T14:09:50.837

I based my answer on this one

I would sort descending by game and use a ranking on it, then wrap your query around this one.

I changed COUNT(*) as count to COUNT(*) as cnt to avoid confusion, count is a SQL keyword

Schema (MySQL v5.7)

CREATE TABLE test (
  `game` INTEGER,
  `winner` VARCHAR(5)
);

INSERT INTO test
  (`game`, `winner`)
VALUES
  (1, 'Mark'),
  (2, 'Joe'),
  (3, 'Mark'),
  (4, 'Paula'),
  (5, 'Paula'),
  (6, 'Paula'),
  (7, 'Joe'),
  (8, 'Anna');

Query #1

SELECT winner, COUNT(*) as cnt, MIN(rank) AS lastwon FROM
(
    SELECT    winner,
              game,
              @curRank := @curRank + 1 AS rank
    FROM      test, (SELECT @curRank := -1) r
    ORDER BY  game DESC
 ) ranking
 GROUP BY winner
 ORDER BY cnt;

Output

| winner | cnt | lastwon |
| ------ | --- | ------- |
| Anna   | 1   | 0       |
| Joe    | 2   | 1       |
| Mark   | 2   | 5       |
| Paula  | 3   | 2       |

View on DB Fiddle

@Berry you are welcome. I love your name, this is the name of the French area I'm from — Cid, Feb 19 '19 at 14:14

Mysql - Count rows until a first distinct value is reached

3 Answers3