sql - Arrange duplicates and number the records in a sequence - MySQL -

my mysql table has below records,

    id name   account -----------------------------------------     1  abc    pqr     2  def    pqr     3  abc    pqr     4  xyz    abc     5  def    pqr     6  def    abc 

i looking output

    id name   account   duplicate sr no. -----------------------------------------     1  abc    pqr       1     2  def    pqr       1     3  abc    pqr       2     4  xyz    abc       1     5  def    pqr       2     6  def    abc       1 

here mean each duplicate should have sr number or number duplicates.

name : abc , account : pqr when repeated in table, there increment in duplicate sr no 1 2

mysql doesn't yet support window function other rdbms. behaviour similar row_number() gives rank number every record in group. in mysql, can simulated using user variables.

select  id, name, account, duplicatesr_no         (             select  id,                     name,                     account,                     @sum := if(@nme = name , @acct = account, @sum ,0) + 1 duplicatesr_no,                     @nme := name,                     @acct := account                tablename,                     (select @nme := '', @sum := 0, @acct := '') vars             order    name, account         ) s order   id 

• sqlfiddle demo

output

╔════╦══════╦═════════╦════════════════╗ ║ id ║ name ║ account ║ duplicatesr_no ║ ╠════╬══════╬═════════╬════════════════╣ ║  1 ║ abc  ║ pqr     ║              1 ║ ║  2 ║ def  ║ pqr     ║              1 ║ ║  3 ║ abc  ║ pqr     ║              2 ║ ║  4 ║ xyz  ║ abc     ║              1 ║ ║  5 ║ def  ║ pqr     ║              2 ║ ║  6 ║ def  ║ abc     ║              1 ║ ╚════╩══════╩═════════╩════════════════╝