Hoon Hoon - 3 months ago 9
MySQL Question

auto increment by specific column's data

In a table like below,


category | feeds | content
==========================
1 1 something
1 2 bar
1 3 foo
2 1 blah
2 2 um
1 4 things


How should I do to work auto_increment independently (distinguished by 'category')?

Thanks in advance.

Answer

When it comes to oracle we have ROW_NUMBER() OVER PARTITION BY statement but for Mysql it is still tricky to implement this one. As most probably we will use this implementation to limit data either descending/ascending order across groups.

Here you go with the similar implementation for your reference:

SELECT @row_number:=CASE WHEN @category=category 
THEN @row_number+1 ELSE 1 END AS feeds,@category:=category AS category, content
FROM table1, (SELECT @row_number:=0,@category:='') AS t
ORDER BY category;

SQLFiddle Link