0

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 >= 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

2
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
0

Here is an answer that works for me

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

Happy to select another answer if someone can significantly clean this up.

http://sqlfiddle.com/#!9/8808649/39

-1
select author_name from 
(
select * from 
(                                      -- emulate row_number()
select ta.*,
if(@typex=ta.author_id, 
@rownum:=@rownum+1, 
@rownum:=1+least(0,@typex:=ta.author_id)) rown 
from Books ta, (select @rownum:=1, @typex:='_') zz
order by author_id, published_date DESC
) yy
where rown < 3                          -- get first 2 records
) zz join authors using(author_id)
group by author_id
having sum(best_seller = 'true') = 2    -- check both are best sellers
; 

fiddle

  • 1
    Thank you Akina. Can you add a bit more prose to explain how it works? – Thunder Rabbit Jun 13 '18 at 13:06
  • @ThunderRabbit Look at the most inner query (aliased as yy). It emulates ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY published_date DESC). It processes records one-by-one in order by author_id, published_date DESC order. If author_id it the same as in previous record it increases @rownum else assign it to 1. Middle subquery abandons the records with numbers of 3 or more. Outer query groups remaining records by author_name and filters the records with both best_seller fields contained 'true'. – Akina Jun 13 '18 at 13:26

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.