MySQL - Get a counter for each duplicate value

Question

I have a table with two columns.

+------+------+
| data | num  | 
+------+------+
| a    |      | 
| a    |      |
| a    |      |
| b    |      |
| b    |      |
| c    |      |
| d    |      |
| a    |      |
| b    |      | 
+------+------+

I want the column "num" displays an incremental counter for each duplicate entry:

+------+------+
| data | num  | 
+------+------+
| a    |    1 | 
| a    |    2 |
| a    |    3 |
| b    |    1 |
| b    |    2 |
| c    |    1 |
| d    |    1 |
| a    |    4 |
| b    |    3 | 
+------+------+

Is this possible to be done without any other scripting besides a mySQL query?

UPDATE:

extended question here

Answer 1

Unfortunately, MySQL does not have windowing functions which is what you will need. So you will have to use something like this:

Final Query

select data, group_row_number, overall_row_num
from
(
  select data,
        @num := if(@data = `data`, @num + 1, 1) as group_row_number,
        @data := `data` as dummy, overall_row_num
  from
  (
    select data, @rn:=@rn+1 overall_row_num
    from yourtable, (SELECT @rn:=0) r
  ) x
  order by data, overall_row_num
) x
order by overall_row_num

see SQL Fiddle with Demo

Explanation:

First, inner select, this applies a mock row_number to all of the records in your table (See SQL Fiddle with Demo):

select data, @rn:=@rn+1 overall_row_num
from yourtable, (SELECT @rn:=0) r

Second part of the query, compares each row in your table to the next one to see if it has the same value, if it doesn't then start the group_row_number over (see SQL Fiddle with Demo):

select data,
      @num := if(@data = `data`, @num + 1, 1) as group_row_number,
      @data := `data` as dummy, overall_row_num
from
(
  select data, @rn:=@rn+1 overall_row_num
  from yourtable, (SELECT @rn:=0) r
) x
order by data, overall_row_num

The last select, returns the values you want and places them back in the order you requested:

select data, group_row_number, overall_row_num
from
(
  select data,
        @num := if(@data = `data`, @num + 1, 1) as group_row_number,
        @data := `data` as dummy, overall_row_num
  from
  (
    select data, @rn:=@rn+1 overall_row_num
    from yourtable, (SELECT @rn:=0) r
  ) x
  order by data, overall_row_num
) x
order by overall_row_num

Answer 2

Here is a simple query that will do what you want.

select id,data,rownum 
  from (
          select id,
                 data,
                 @row:=if(@prev=data,@row,0) + 1 as rownum,
                 @prev:=data 
            from tbl
        order by data,id
)t

I have included an id on each row. But you don't need it.

Go fiddle: http://sqlfiddle.com/#!2/1d1f3/11/0

Credit: Want Row Number on Group of column in MY SQL?