mysql - Per group, find first N users with SUM(x) >= N -

problem: find first 2 users have @ least 10 items in category, per category.

table structure:

create table items(     id int auto_increment primary key,     datetime datetime,     category int,     user int,     items_count int ); 

 

sample data:

insert items (datetime, category, user, items_count) values ('2013-01-01 00:00:00', 1, 1, 10), ('2013-01-01 00:00:01', 1, 2, 1), ('2013-01-01 00:00:02', 1, 3, 10), ('2013-01-01 00:00:03', 1, 2, 9),  ('2013-01-01 00:00:00', 2, 4, 10), ('2013-01-01 00:00:01', 2, 1, 10), ('2013-01-01 00:00:01', 2, 5, 10); 

 

desired result:

category    user 1           1 1           3 2           4 2           5 

 

note: shown in result, need able show preference towards user when multiple users meet requirements simultaneously.

 

sql fiddle:

http://sqlfiddle.com/#!2/58e60

 

this have tried:

select   derived.*,   if (@category != derived.category, @rank := 1, @rank := @rank + 1) rank,   @category := category   from(   select     category,     user,     sum(items_count) items_count,     max(datetime) datetime     items     group     category,     user    having     sum(items_count) >= 10 ) derived   join(select @rank := 0, @category := 0) r   having   rank <= 2  order   derived.category,   derived.datetime 

 

but faulty. not not take user precedence account, produce wrong result data such this:

('2013-01-01 00:00:00', 1, 1, 10), ('2013-01-01 00:00:01', 1, 2, 1), ('2013-01-01 00:00:02', 1, 3, 10), ('2013-01-01 00:00:03', 1, 2, 9), ('2013-01-01 00:00:10', 1, 3, 1); 

 

additional information: not know if procedures make difference in scenario, unfortunately not option either. user running query has select privilege.

in order find users meet needs, need cumulative sum of counts. following query finds occasions when user first reaches 10 units. if counts never negative, there one:

select i.* (select i.*,              (select sum(items_count)               items i2               i2.user = i.user ,                     i2.category = i.category ,                     i2.datetime <= i.datetime              ) cumsum       items      ) cumsum - items_count < 10 , cumsum >= 10 order datetime; 

to first two, need use mysql tricks counting within group. here example works:

select i.* (select i.*, if(@prevc = category, @rn := @rn + 1, @rn := 1) rn, @prevc := category       (select i.*,                    (select sum(items_count)                     items i2                     i2.user = i.user ,                           i2.category = i.category ,                           i2.datetime <= i.datetime                    ) cumsum             items             )            cross join            (select @rn := 0) const       cumsum - items_count < 10 , cumsum >= 10      ) rn <= 2 order category, datetime; 

i have problem approach, because nothing in mysql says expression @prevc := category calculated after calculation rn. however, seems case, , seems work in practice.