【Question】

A 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 the table. I want to write a query to find 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 don’t want to group OCCURENCE_CONTER by key. I just want to get ungrouped counter.

Is this possible in one single query (or with some subqueries)?

【Answer】

It’s easy to get it done with a variable in MySQL. It’s also simple to create intra-group sequence numbers with ranki function in SPL (Structured Process Language):

A1: Retrieve data by id in SQL.

A2: Add OCCURENCE_COUNTER column and use ranki(id;key) to create sequence numbers for records with same keys. Here’s the final result: