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?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments