Mysql make count value increment with 1

Question

I want to add multiple counter in other column. I have a two row in mysql database and i have multiple id in second column so i want to add counter like 1,2,3... in other column.

my screenshort are given below :

In image are 1889 are twice and i want these two means all duplicate value make increment with 1.

expected output : 1889 dell1 1889 dell2 1890 dell0 1891 dell0

Above 1889 are twice so output is 1889 dell1 ,1889 dell2. 1890 and 191 is single so output is 0 behind dell.

so i want increment with 1 behind dell.

if anyone know mysql query so please inform me.

Are you sure you want 1989 dell 1, 1889 dell2 and not ```1989 dell 0, 1889 dell1``` ? — Sandesh Gupta, Mar 21 '18 at 12:02
This is a very common example of ranking records. You can find lot of resources online if you google it. You can start with this link. http://www.folkstalk.com/2013/03/grouped-rank-function-mysql-sql-query.html — Sandesh Gupta, Mar 21 '18 at 12:28
I think u did not understand my question. I need a prefix with increment after dell based on multiple ids. — Akki, Mar 21 '18 at 15:08

score 0 · Answer 1 · answered Mar 22 '18 at 04:19

The question that you are mentioning it here is an example of rank by partition. Oracle has in-built functions to obtain such output, but MySQL doesn't have something like that.

select result.YEAR, CONCAT(result.NAME,'',result.rank) from (
  SELECT    name,
              year,
              IF(year=@last,@curRank:=@curRank+1,@curRank:=0) AS rank,
              @_sequence:=@_sequence+1,
              @last:=year
    FROM      COMPUTER , (SELECT @curRank := 1, @_sequence:=1, @last:=0) r
    ORDER BY  year asc) as result;

You can refer the fiddle here

I have taken reference from this another answer here.

Mysql make count value increment with 1

1 Answers1