I have a table as

id  num_items  folder
1   4
2   33
3   74
4   41
5   24
6   34
7   46
8   55
9   11

I want to assign folder number to rows but limiting the number of items in each folder (e.g. 100). Thus, increasing the folder number (next folder) if SUM(num_items) > 100.

The UPDATEd folder should look like

id  num_items  folder
1   4          1
2   33         1
3   74         2
4   41         3
5   24         3
6   34         3
7   46         4
8   55         4
9   11         5

Since, SUM make the calculation for the entire column, apparently, I need to reset the value/process of SUM after reaching the maximum item in folder.


One option might be using user-variables:

SET @folder = 1;
SET @items = 0;
SELECT id, num_items, 
  (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1,   @folder)) as folder, 
  IF(@items>100,@items:=0,@items) as checkItems
FROM foo;

Here's an sql fiddle of it, though your example id=4 does not match. In the first block, it's 44, in the expected output block it is 41, so the folders are off.

Adding a caveat here. According to the documentation on user variables:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.

Just keep that in mind.

Edit by RolandoMySQLDBA (2012-12-17 14:30 EDT)

So as to be sure of what the user variables look like, perhaps have each of the user variables printed along the way : ( See Example )

SET @folder = 1;
SET @items = 0;
SELECT id, num_items, 
  @folder as CurrentFolder_Before,
  @items as CurrentItems_Before,
  (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1,   @folder)) as folder, 
  IF(@items>100,@items:=0,@items) as checkItems,
  @folder as CurrentFolder_After,
  @items as CurrentItems_After
FROM foo;

DTest gets a +1 for his application of user variables.

| improve this answer | |
  • yes I was thinking of user-variable, but I wished not to use sub-query, as it is slow in long tables (which is my case). But it seems, I have no other choice! – Googlebot Dec 17 '12 at 18:40
  • 2
    Subqueries and being slow are not connected in any way. Most probably you can speed them up if they are really slow. – dezso Dec 17 '12 at 18:54
  • Thanks for two versions, but please note that the question originally asked for UPDATE the table, rather than SELECTing the data. – Googlebot Dec 17 '12 at 20:22
  • @dezso why subquery is not slow? subquery needs to conduct the process row by row. Am I wrong? – Googlebot Dec 17 '12 at 20:24
  • @All Have you tried it? – dezso Dec 17 '12 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.