Reputation: 734
I have a query:
SELECT reply.id,
reply.message,
reply.userid,
reply.date,
medal.id,
medal.url,
medal.name,
user.id,
user.name AS username
FROM posts AS reply
LEFT JOIN users AS user ON reply.userid = user.id
LEFT JOIN medals AS medal ON medal.userid = user.id
GROUP BY reply.id
ORDER BY reply.id ASC
everything is OK, except that I get the medal ascending not descending which mean that it grab the first medal that user got - I need to get the last one.
Upvotes: 2
Views: 1596
Reputation: 676
The fact that you are seeing the first record per group is accidental. Selecting a non-aggregate, non-group-unique column in a GROUP BY query causes an undefined record to be selected.
For a more detailed explanation read: http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html.
One correct way to do what you're doing is by using a subquery, in which you select the maximum medal date per desired group.
This approach is outlined here: http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html
Upvotes: 1
Reputation: 11395
The simplest solution is to do this:
ORDER BY reply.id ASC, medal.id DESC
AND remove your GROUP BY
statement.
You can filter the posts / users who have multiple medals afterwards (skip rows that contain the medals you don't want).
The next option is to select MAX(medal.id)
in a subquery, and then join on that. That gets messy, but is doable. Here is the general idea (you will probably have to rename a few table aliases, since they are used more than once):
SELECT *,
medal.url,
medal.name
FROM
(
SELECT reply.id,
reply.message,
reply.userid,
reply.date,
MAX(medal.id) AS medal_id,
user.id,
user.name AS username
FROM posts AS reply
LEFT JOIN users AS user ON reply.userid = user.id
LEFT JOIN medals AS medal ON medal.userid = user.id
GROUP BY reply.id
ORDER BY reply.id ASC
)
AS t
LEFT JOIN medals AS medal ON medal.id = t.medal_id
Upvotes: 0
Reputation: 157
You could perhaps make a subquery or temporary table for medals instead of joining directly on medals. The temporary table would be the medals table but sorted DESC. The subquery would be LEFT JOIN (select id, url, name from medals order by desc) but I feel like that would be very slow and not the best option.
Upvotes: 0