Get Primary records where N most recent Secondary records match condition I want a list of Authors (primary records) whose 2 most recent Books (secondary records) are best sellers.

INSERT INTO Authors
    (`author_id`, `author_name`)
VALUES
    (41, 'Heida Palffrey'),
    (42, 'Michael Grist'),
     ...

INSERT INTO Books
    (`book_id`, `author_id`, `book_title`, `published_date`, `best_seller`)
VALUES
(156, 41, 'Red Heat',      '2007-01-21', 'false'),
(161, 41, 'Far From Home', '2003-12-27', 'true'),
(859, 41, 'Storm Warning', '1992-08-19', 'true'),
(914, 41, 'Dead Heat',     '1981-01-04', 'false'),

(780, 42, 'The Last',      '2017-01-31', 'true'),
(198, 42, 'Mr Ruin',       '2016-01-18', 'true'),
(166, 42, 'Mr. Troop Mom', '1982-06-27', 'false'),
(796, 42, 'Ben Bootin',    '1972-12-01', 'false'),

    ...


For the above data, I just want author "Michael Grist" whose two most recent books were best sellers.

I have been able to get authors with consecutive best sellers, but not authors whose most recent two books were best sellers.

SELECT t.author_id, a.author_name, t.consecutive, t.best_seller, COUNT(1) consecutive_count
FROM (
  SELECT b.* ,
    @r:= CASE WHEN @g = b.`best_seller` THEN @r ELSE @r + 1 END consecutive,
    @g:= b.`best_seller` g
  FROM books b
  CROSS JOIN (SELECT @g:='true', @r:=0) t1
  ORDER BY author_id, published_date
) t
JOIN `authors` `a` ON (`a`.author_id = t.author_id)
GROUP BY t.author_id,t.consecutive,t.`best_seller`
HAVING consecutive_count &gt;= 2 AND  t.`best_seller` = 'true';


For the data in the sqlfiddle below, only Chaddie Dreakin and Michael Grist fit the requirements of two most recent best sellers.

http://sqlfiddle.com/#!9/8808649/8
SET @@group_concat_max_len = 9;

SELECT  a.author_name
    FROM  
    (
        SELECT  author_id,
                GROUP_CONCAT(best_seller ORDER BY  published_date DESC) AS bs
            FROM  Books
            GROUP BY  author_id
            HAVING  bs = 'true,true' 
    ) AS x
    JOIN  Authors AS a USING(author_id) ;


Notes:


9 is based on true,true being 9 characters, and that is enough to catch the first two.
I'm assuming best_seller is a VARCHAR.
ORDER BY  published_date DESC -- the "latest"
HAVING -- can't use WHERE in this situation