How to add auto increment id according to a group in mysql

Question

Here is the format of the table:

indexer group name id
1       abc   a
2       abc   b
3       xyz   c
4       abc   e
5       xyz   d

Now i want it to be like,

indexer group name id
1       abc   a    1
2       abc   b    2
3       xyz   c    1
4       abc   e    3
5       xyz   d    2

"id" should auto increment according to "group"

Answer 1

Try this:

update yourtable t1
join (
    select
          tt.indexer, @rowno := if(@grp = `group`, @rowno + 1, 1) as id, @grp := `group`
    from (select * from yourtable order by `group`, indexer) tt
    cross join (select @rowno := 0, @grp := null) t
) t2
on t1.indexer = t2.indexer
set t1.id = t2.id

Demo Here

Edited:

If you want to insert a new row, you have to do it like this:

insert into yourtable
select '$indexer', '$group', '$name', coalesce(max(id), 0) + 1
from yourtable
where name = '$name'

Answer 2

If you want to use a built in function, you have to use a MyISAM table. Be aware though, that those do not support transactions and use table level locks and so on. You might want to read about that. If you're fine with it, here's how. If you want to use InnoDB or other engines, you will have to write your own solution, i.e. a stored procedure.

Quote from the manual:

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

• In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.

• If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.