auto increment every 10 rows

Question

I'm using PHP. I will create a a column in my database that will have a value, such as 1. I want to set this value to auto-increment each time it passes 10 rows:

In other words:

1st row will have the value = 1
2nd row will have the value = 1
........
........

10th row will have the value = 1 

11th row the value will auto increment and change to be = to 2

Don't bother storing this kind of data. Just calculate it 'on-the-fly' — Strawberry, Apr 19 '16 at 16:06
@Andrew 38 - If you mention your use with that column value, I think I can give you a better solution — Fathah Rehman P, Apr 19 '16 at 16:08
Create two fields, the first is the classic auto increment and the second should be populated with a stored procedure. — manuerumx, Apr 19 '16 at 16:09
As @Strawberry said, this is probably better implemented as a calculated field in a query. How will you be consuming the data? — snorkpete, Apr 19 '16 at 16:12
Getting a good grib of the consumption is hard, it will depend. — Andrew 38, Apr 19 '16 at 16:14
what defines the ORDER of these rows? (could you possibly insert a row between 5 and 6 after the 11th row has been generated? causing 10 to become 11 and thus needing to change to 2?) — xQbert, Apr 19 '16 at 16:14
Perform (rownum/10) +1 and decide the value to be stored. For eg, 6th row, 6/10 +1 = 1, 100th row,= 11 and so on. No access to editor. Else would have answered — I_am_Batman, Apr 19 '16 at 16:17
@I_am_Batman that would be false for rows = 10 , 20, 30, it would give id 2,3,4 etc. — Martin, Apr 19 '16 at 16:23
@martin.. I stand corrected. But that can be tweaked around a bit. — I_am_Batman, Apr 19 '16 at 16:26
My code a few comments up does what you're stating :-) @I_am_Batman — Martin, Apr 19 '16 at 16:30

Fathah Rehman P · Accepted Answer · 2016-04-19T18:25:35.447

2

Here is the solution for you

Assume you have a table named table1 and that table have following structure

CREATE TABLE `table1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)

Note that id column is auto increment

Following query will give you the result you want

select a.id,FLOOR((a.id-1)/10)+1 as calculated_value from table1 a

In the result calculated_value will give you the result.

If you are interested, some additional information

Assume you fear that taking auto increment column for calculation is risky then you can use following

    SELECT t.id,t.name ,(@num:=@num+1) AS i,FLOOR((@num-1)/10)+1 
as calculated_value  FROM table1 t CROSS JOIN (SELECT @num:=0) AS 
dummy ORDER BY id;

edited Apr 19 '16 at 18:25

answered Apr 19 '16 at 16:17

Fathah Rehman P

8,401
4
40
42

Note that this solution assumes no gaps/deletions – Strawberry Apr 19 '16 at 16:18
you could bundle your example in a sequence and it will work. see this example : http://stackoverflow.com/questions/26578313/how-do-i-create-a-sequence-in-mysql – mongotop Apr 19 '16 at 16:24
@Andrew38 - can you explain that with an example? – Fathah Rehman P Apr 19 '16 at 16:36
@Andrew38 - Kindly provide your table's structure too. Also mention using which column you are ordering your output – Fathah Rehman P Apr 19 '16 at 16:46
@Andrew38 - You mentioned some boolean values too right? I cant see that in your last comment. Can you add your table structure in your question? – Fathah Rehman P Apr 19 '16 at 16:59
@Andrew38- In this case your select statement should use order by some column right? Which column you are using for that? – Fathah Rehman P Apr 19 '16 at 17:10
@Andrew38 - You need to select data from your table for processing. In this case it is better to specify and order by condition in your select query. For example select * from table1 order by id. Do you have any requirement like that ? – Fathah Rehman P Apr 19 '16 at 17:13
@Andrew38 - you will read row with seen=0 then after processing you will make seen=1. is that happening in your case? – Fathah Rehman P Apr 19 '16 at 17:27
i'm calculating auto increment value and then calculating your required number – Fathah Rehman P Apr 19 '16 at 17:53
@Andrew38- is that problem solved? – Fathah Rehman P Apr 19 '16 at 18:10
What does the t.id, t.name and @cnt refer to ? – Andrew 38 Apr 19 '16 at 18:23
@Andrew38 - that query using my sample table mentioned in first part of the answer – Fathah Rehman P Apr 19 '16 at 18:24

auto increment every 10 rows

1 Answers1