Get records for the maximum count grouped results among all group

Question

I have three tables book, member, and loan. I want to find the firstname, lastname and title of the books which member has maximum number of books. I tried the following

1. To get the member who has maximum records, I grouped the records based on member_id and it decending the order to get the max count. The grouped record contains only one member_id(3) but I want all member_id (2 and 3) to get the "title of the book" so I used GROUP_CONCAT to get it.

SELECT m.firstname, m.lastname, l.member_id, GROUP_CONCAT(l.book_id) as book_id, count(*) as max_c from member m
join loan l
ON m.member_id = l.member_id
group by l.member_id
order by max_c desc limit 1

2. Then I tried to get the desired records using the below query

SELECT m.firstname, m.lastname, b.title from book b
join loan l ON b.book_id = l.book_id
where l.book_id IN
(SELECT m.firstname, m.lastname, l.member_id, GROUP_CONCAT(l.book_id) as book_id, count(*) as max_c from member m
join loan l
ON m.member_id = l.member_id
group by l.member_id
order by max_c desc limit 1)

The desired output is The output is expected as given below

firstname   lastname    title
Mark        Sacks       The Cow 
Mark        Sacks       XML for beginners

I'm trying this in SQLite(SQL.js) in sqlfiddle pls help me to achieve this.

book table

book_id     isbn            title               author      publish_year
1           444222666325    Mars                Mark Sas    2001
2           784566512135    The Cow             Williams    1996
3           488984115444    XML for beginners   Jake Snow   2005
4           544465545655    Into Thin Air       Jon Kraka   1990
5           878745656513    And Tango Makes     PeterParnel 1956
6           564564123213    Swimmy  Leo         Lionni      2010
7           132125645678    XML and XQuery      Lee Cakes   2014
8           132154548746    Happy Places        Steve Zus   1998
9           788897998754    The Mascot          Kevin Bacon 1987
10          878561132116    XQuery for begin    Virginia    1800

member table

member_id   lastname    firstname
1001        Smith       John
2123        Sacks       Mark    
3456        Johnson     Susan   
4223        States      Nick    
5987        Stew        Martha  

loan table

member_id   book_id loan_date   due_date
5987           4    2017-09-13  2017-09-30
2123           3    2017-09-13  2017-09-15
4223           9    2017-09-13  2019-09-13
1001           5    2017-10-15  2017-10-19
2123           2    2017-10-15  2017-11-15

Answer 1

You want to use COUNT here as an analytic function, to retain all rows, then apply RANK to find the highest ranking author along with all records:

WITH cte AS (
    SELECT m.firstname, m.lastname, b.title, COUNT(*) OVER (PARTITION BY m.member_id) cnt
    FROM member m
    INNER JOIN loan l ON l.member_id = m.member_id
    INNER JOIN book b ON b.book_id = l.book_id
),
cte2 AS (
    SELECT *, RANK() OVER (ORDER BY cnt DESC) rnk
    FROM cte
)

SELECT firstname, lastname, title
FROM cte2
WHERE rnk = 1;

Demo