0

Suppose I have a table of User

User :

id name
1 Alisa
2 Bryce
3 Ryan

and a table of Book

Book:

id title
1 Book 1
2 Book 2
3 Book 3
4 Book 4
5 Book 5
6 Book 6

and a table of Loan:

id user_id book_id
1 1 1
2 1 2
3 2 3
4 2 4
5 3 5
6 3 6

I have created a query that allows me to see users with their borrowed book. The query code is as below:

SELECT u.name as User, 
       GROUP_CONCAT(b.title) as Borrowed_Books
  FROM Loan l
  JOIN User u ON u.id = l.user_id 
  JOIN Book b ON b.id = l.buku_id
 GROUP BY u.id;

The result would look like this:

User Borrowed_Book
Alisa Book 1,Book 2
Bryce Book 3,Book 4
Ryan Book 5,Book 6

But I want it so the grouped books are ordered descending (Book 2, Book 1; Book 4, Book 3; etc...). How could I do so? I tried using ORDER BY b.title DESC but it would give me an error.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
BlancHg
  • 1

1 Answers1

1

You can provide ORDER BY clause following the argument of the GROUP_CONCAT() function
such as

SELECT u.name as User, 
       GROUP_CONCAT(b.title ORDER BY b.title DESC) as Borrowed_Books
  FROM Loan l
  JOIN `User` u ON u.id = l.user_id 
  JOIN Book b ON b.id = l.book_id
 GROUP BY u.name

Demo

As a side note : All non-aggregated column(s)(in this case u.id[provided that it would be needed of course]) should be listed next to GROUP BY for only_full_group_by mode of SQL

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55