MYSQL - Ungrouped Counter of Column value Occurence

Question

Mysql Table ordered by key asc:

id | key | value | OCCURENCE_COUNTER
--------------------------------------------------------
 1    a     ...          1
 2    a     ...          2
 3    a     ...          3
 4    b     ...          1
 5    b     ...          2
 6    b     ...          3
 7    c     ...          1
 8    c     ...          2
 9    c     ...          3

Column OCCURENCE_COUNTER does not exist in table. I want to make Query, which could tell me about value of OCCURENCE_COUNTER for every row:

// pseudo-code:
foreach(row) {
    if(isFirstOccurenceOfKey(current_key)) {
        current_OCCURENCE_COUNTER = 1;
    } else {
        current_OCCURENCE_COUNTER = previous_OCCURENCE_COUNTER + 1;
    }
}

I want not group OCCURENCE_CONTER by key. I just want to get ungrouped counter.

In this possible to do in one single query (or with some subqueries)?

Answer 1

I hope, I understand your question correctly. occurence_conter is the table you defined in the question.

select c.*, 
    ( select count(*) 
        from occurence_conter c1
        where c1.key = c.key
            and c1.id <= c.id ) as OccurenceCounter
from occurence_conter c;