increment start from 7 and based on two columns changes

I have a set of data as below:

product	repack
1	44
1	44
2	0
2	0
3	0
3	0

Trying to get a result like:

product	repack	increment
1	44	7
1	44	8
2	0	7
2	0	8
3	0	7
3	0	8

Please help, thanks in advance.

CodePudding user response：

SELECT product, repack,
  ROW_NUMBER() OVER (PARTITION BY product)   6 AS `increment`
FROM MyTable;

ROW_NUMBER() is a window function, and requires at least MySQL 8.0.

CodePudding user response：

This is for older versions:

SELECT m.*,
       CASE WHEN product=@product THEN @rnk := @rnk 1
        ELSE @rnk := 7 END AS rn,
        @product := m.product
FROM mytable m
CROSS JOIN (SELECT @rnk := 7, @product := NULL) r
ORDER BY product, repack;

Demo fiddle

P/S: I can understand why some company still using old MySQL version, in fact the current company I work at was using MySQL v4.1 until just recently. It took me a while convincing them to upgrade - which includes time consuming test scenarios and (maybe) costly but necessary hardware upgrades. Fortunately, they're onboard with it and although we're not exactly changing from MySQL to MySQL (now we're using MariaDB 10.3 ) but luckily the differences between both are quite small for us to notice. What's important though, it greatly improve our efficiency. But if this is just your own (local) database, you should definitely consider upgrading. If you're using a third-party database, well, that's a different story.