group by - mysql multiple grouping on one table -

i have 1 table job_result

create table job_result  (   node varchar(20) default null,   jobid int(10) default null,   subresult int(1) default null )  

the table contains many nodes.

each node has many jobid.

each jobid has many subresults (usually less 10).

some example data provided

insert job_result values ('a', 14, 0); insert job_result values ('a', 15, 0); insert job_result values ('a', 16, 1); insert job_result values ('a', 17, 0); insert job_result values ('a', 18, 1); insert job_result values ('a', 19, 1); insert job_result values ('a', 20, 0);   insert job_result values ('b', 1, 0); insert job_result values ('b', 2, 0); insert job_result values ('b', 3, 1); insert job_result values ('b', 4, 1); insert job_result values ('b', 5, 1); insert job_result values ('b', 6, 1); insert job_result values ('b', 7, 1);   insert job_result values ('c', 10, 0); insert job_result values ('c', 11, 0); insert job_result values ('c', 12, 0); insert job_result values ('c', 13, 0); insert job_result values ('c', 14, 0); insert job_result values ('c', 15, 0); insert job_result values ('c', 16, 0); 

i want query give me "health check" on each node see if node has had x consecutively failed jobs. failed job jobid have @ least 1 non-zero result.

select node, jobid, sum(subresult) res job_result node = 'a'  group jobid  order jobid desc  limit 5 

this gives me last 5 job's result on node a, 0 indicates job.

+------+---------+------+ | node | jobid   | res  | +------+---------+------+ |    |   20    |   0  | |    |   19    |   1  | |    |   18    |   1  | |    |   17    |   0  | |    |   16    |   1  | 

then need sum , group results 1 row per node , got far:

select node, sum(i.res) res (select node, sum(subresult) res job_result node = 'a'  group jobid  order jobid desc  limit 5)  +------+------+ | node | res  | +------+------+ |    |  3   | 

my question is: how can expand query return 1 row per node? have tried several days correlated sub queries , joins, fail due inner select cannot see node select on. , when in place, work on changing limit 5 dynamic value.

desired result like

+------+------+ | node | res  | +------+------+ |    |  3   | | b    |  0   | | c    |  1   | | e    |  0   | 

one row each node, right column showing number of fails

please advise!

in mysql, on large table, best approach getting last 5 use variables enumerate results. rest aggregation:

select node, sum(subr > 0) numfails (select node, jobid, subr,              (@rn := if(@n = node, @rn + 1,                         if(@n := node, 1, 1)                        )              ) rn       (select node, jobid,                    sum(subresult) subr             job_result jr             group node, jobid            ) jr cross join            (select @n := '', @rn := 0) init       order node, jobid desc      ) nr rn <= 5 group node;