how to repeat serial number in mysql table

Question

I have following mysql table

 name   city
umar     mzd
ali      mzd
john     bagh
saeed    bagh
irum     bagh

I want to add serial number in way that serial number repeat when city name changed as follow

S.No   name     city
  1    umar      mzd
  2    ali       mzd
  1   john      bagh
  2   saeed     bagh
  3   irum      bagh

When you select data this is possible when simulating RANK() OVER PARTITION in MySQL but you need to use user variables but this is an heavy query for MySQL to handle if the table contains an lot off records — Raymond Nijland, Oct 12 '13 at 14:43

Ben · Answer 1 · 2013-10-12T22:32:49.603

Here, give this a try:

select name, city, serial_number from
    (select name, city
    , (case when @city <> city then @citynum := 1 else @citynum := @citynum + 1 end) as serial_number
    , (case when @city <> city then @city := city end) as v
    from tbl_cities, (select @citynum := 0, @city := '') as c
order by city) as cities

Here is a link to your example running: http://sqlfiddle.com/#!2/615f5/1.

You will need to change 'tbl_cities' to the name of your table.

I could not get it to save as a view or use in an update statement as it uses variables. One quick way would be to use the select statement above to create a new table and run periodically as new data is added.

drop table if exists tbl_cities_serial;

create table tbl_cities_serial(select name, city, serial_number from
(select name, city
    , (case when @city <> city then @citynum := 1 else @citynum := @citynum + 1 end) as serial_number
    , (case when @city <> city then @city := city else @city end) as v
from tbl_cities, (select @citynum := 0, @city := '') as c
order by city) as cities)

how to repeat serial number in mysql table

1 Answers1