mysql left join ascending

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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