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
- 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
- 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
You want to use
COUNT
here as an analytic function, to retain all rows, then applyRANK
to find the highest ranking author along with all records:Demo