Heider Kumar Heider Kumar - 4 months ago 27
MySQL Question

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

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"

JPG JPG
Answer

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'