MySQL - Selecting Users With A List Of Their Borrowed Book In DESC Ordering [duplicate]
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.
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