获取MySQL LEFT JOIN上最后一次出现的行

Question

I have the following tables in my Database:

USERS

+-------+-------------------------------------+
|   id  |                name                 |
+-------+-------------------------------------+
|   1   | Johnny Appleseed                    |
|   2   | Pete Jones                          |
|   3   | John Doe                            |
|   4   | Jane Plick                          |
+-------+-------------------------------------+

REPORTS

+-------+-------+-----------------------------+
|   id  | owner | title                       |
+-------+-------+-----------------------------+
|   1   | 1     | Weekly report #86           |
|   2   | 1     | Weekly report #87           |
|   3   | 1     | Weekly report #88           |
|   4   | 2     | Weekly report #1            |
|   5   | 3     | Weekly report #33           |
|   6   | 3     | Weekly report #34           |
+-------+-------------------------------------+

What I need to do is GROUP the results by first name, so that the list itself is alphabetical order, but I need the LAST occurrence of the row matching the user's id.

The "owner" column of the REPORTS table matches the "id" column of the USERS table.

My desired results look like:

Jane Plick       |     
John Doe         | Weekly Report #34
Johnny Appleseed | Weekly Report #88
Pete Jones       | Weekly Report #1

My current query ALMOST works, however it only shows the FIRST weekly report for that user, not the last.

SELECT * FROM users AS a LEFT JOIN ppp_reports AS b ON a.id=b.owner WHERE a.active=1 GROUP BY a.id ORDER BY a.firstname ASC

I have tried a lot of different variations, but I'm always left with the FIRST row from the REPORTS table.

Any help is greatly appreciated.

Answer 1

In your particular case where all reports are named "Weekly report #X", you can try this:

SELECT a.id, a.name, MAX(b.title)
  FROM users AS a
  LEFT JOIN ppp_reports AS b ON a.id=b.owner
 WHERE a.active=1
 GROUP BY a.id, a.name
 ORDER BY a.name ASC

See this fiddle.

If the reports may have other names, you'll have to find another way to discriminate the last report from the others.