Getting status by N last rows from each group in MySQL

Question

Here is a simplified table ( sqlfiddle ):

id  group   status
--  ------  -------
1   group1  success
2   group1  fail
3   group1  success

4   group2  success

5   group3  fail
6   group3  success
7   group3  success
8   group3  success

I need to get information about failures for each group of N last lines. How is it (N = 3):

group   has_failures
------  ------------
group1  1
group2  0
group3  0

Getting groups is pretty simple:

-- Select last row from each group:
SELECT a.id,  a.group, a.status
FROM log a
INNER JOIN(
    select max(i.id) as max_id 
    from log i
    group by i.group
) as b on a.id = b.max_id;

And the errors too:

-- Select fail status by last 3 rows:
select count(g.status) > 0 as has_failures
from (
       SELECT a.group, a.status
       FROM log a
       WHERE a.group = 'group3'
       ORDER BY id DESC
       LIMIT 3
     ) as g
where g.status = 'fail';

How should these two queries be combined, or maybe a simpler way exists?

Answer 1

Here, one parameter is used user defined variablesto set the line number for each group. Then you can use conditional aggregation:

select max(t.id) id,
  t.group,
  max(case when t.rank = 1 then t.status end) status,
  sum(case when t.status = 'fail' then 1 else 0 end) hasfailures
from (
  select *, 
    case l.group 
      when @curGroup 
      then @curRow := @curRow + 1 
      else @curRow := 1 AND @curGroup := l.group 
    end + 1 AS rank
  from `log` l cross join (select @curRow:=0, @curGroup:='') c
  order by l.group, id desc
  ) t
where rank <= 3
group by t.group

• SQL Fiddle Demo