1

I have a table with data that has an indicator as a number, it is not an ID or foreign key. This number is repeated several times in the table.

I need to print all the rows with included incremental row number within the same indicator value, so 1 is there 2 times, 2 is there 3 times, 3 is there 1 time.

Desired output:

name | indicator | rownumber
a        1        1
b        1        2
c        2        1
d        2        2
e        2        3
f        3        1

I have found this solution to count the rows but I do not know how to reset the counter if the indicator is changed.

The query I have so far is but this is incrementally counting the rows 

SELECT name, indicator, 
@rownum := @rownum + 1 as row_number
FROM rownumtable
CROSS JOIN (SELECT @rownum := 0) r
ORDER BY name ASC

BUT it prints the row number - see it on SQL Fiddle

name | indicator | row_number
a         1         1
b         1         2
c         2         3
d         2         4
e         2         5
f         3         6

Is there a way how to reset the row_number counter for specific group of same values in the MySQL query?

ino
  • 2,345
  • 1
  • 15
  • 27

1 Answers1

1
SELECT name, indicator,        
       @rownum := case when @prevIndicator <> indicator then 1 else @rownum + 1 end as rownumber,
       @prevIndicator := indicator
FROM rownumtable
CROSS JOIN (SELECT @rownum := 0, @prevIndicator := 0) r
ORDER BY name ASC

SQLFiddle demo

juergen d
  • 201,996
  • 37
  • 293
  • 362