Numbering MySQL Result Row

Question

i have a mysql result set like bellow

id        name
---------------------
1         abc
1         abc
2         xyz
2         xyz
3         pqr

now i need to modify the result set so that it would be like as follows:

id        name
---------------------
1         abc-1
1         abc-2
2         xyz-1
2         xyz-2
3         pqr

the summarization is i need to numbering the name column from result set and the numbering will only apply on the rows which have more than one value in the result set.

so what will be the MySQL SELECT Query?

Answer 1

I see 3 possible ways to resolve this:

1. Update all values in DB once, and then check on each insert/update/delete, whether numbering has to be updated. As far as I know, this can be done either in program, which uses database, or with triggers

2. Generate numbering with SQL query - this will require join table with itself on id, group results and count amount of rows with similar id. This will look in the following way: select t1.id, t1.name, count(t2.id) from t1, t2 where t1.id = t2.id group by t2.id (BTW, this query may take O(N^2) time, which is long for large table) Then you'll have to analyze third column and get final result.

3. Do post-processing of result in your program by means of programming language. This looks like the most easy and efficient solution.

Answer 2

this might work but there's probably a better way that i can't think of right now

set @i = 0;

select
 f.id,
 @i:=@i+1 as i,
 case 
  when c.counter <= 1 then f.name 
  else concat(f.name,'-',(@i % c.counter) +1)
 end as name
from
 foo f
join (select id, count(*) as counter from foo group by id) c on c.id = f.id
order by
 f.id,name;

EDIT: as stated in comments above, better to do this at the app level vs. db