CODEWITHSUNDEEP
phpmysqlgreatest-n-per-group
Spencer
Spencer

Reputation: 597

MySQL: Group By Clause

I need some help with a group by mysql query clause.

Medals Table (this holds all the medals):

+-----------------------------------------------------------------------+
| medal_id | medal_level | medal_name      | medal_type | medal_icon    |
|-----------------------------------------------------------------------|
|        1 |           1 |    1 post medal |          1 | icon_file.png |
|        2 |           2 |  1 thread medal |          2 | icon_file.png |
|        3 |           1 |    2 post medal |          1 | icon_file.png |
|        4 |           2 | 2 threads medal |          2 | icon_file.png |
+-----------------------------------------------------------------------+

Users Medals Table (this holds the medals which users have won):

+--------------------------------+
|medal_id |user_id | earnedtime  |
|--------------------------------|
|       1 |      1 | 1313548360  |
|       2 |      1 | 1313548365  |
|       3 |      1 | 1313548382  |
|       4 |      1 | 1313548410  |
+--------------------------------+

MySQL Query:

SELECT m.*, u.*
FROM users_medals u
LEFT JOIN medals m ON (u.medal_id=m.medal_id)
WHERE u.user_id IN(1)
GROUP BY m.medal_type
ORDER BY u.earnedtime

What this is intended to do is display medals users have earned (this is a plugin for a bulletin board system). It selects and displays the medals where the users medal id is equal to the medal id in the table that holds all the medals.

This works fine, however, it's not displaying the latest medal. It's only displaying the following medal id's: 1, 2. It should be displaying 3 and 4.

Additional Info: I only want to display one medal from each medal type. So for example, if the user has earned two "post medals", only the latest one earned will be displayed, along with any other medals earned.

Any help would be greatly appreciated.

Upvotes: 2

Views: 468

Answers (5)

Neil
Neil

Reputation: 55382

SELECT m.*, u.*
  FROM users_medals u
  LEFT JOIN medals m ON u.medal_id = m.medal_id
  WHERE u.user_id IN (1)
  AND u.earnedtime = (
    SELECT MAX(users_medals.earnedtime) FROM users_medals
    LEFT JOIN medals ON users_medals.medal_id = medals.medal_id
    WHERE users_medals.user_id = u.user_id
    AND medals.medal_type = m.medal_type
  )

I think it should be possible to do this as a subjoin too but the restriction of the medal type being in another table made my head ache. It would probably be easier to write if there was a view of the two tables joined together.

Upvotes: 1

Scorchio
Scorchio

Reputation: 2871

Dalen did ask the right thing - why would you like to use GROUP BY in the first place? That's not needed in this query. GROUP BY serves a different purpose; maybe you can understand it more by checking an example like this one.

Upvotes: 0

zigdon
zigdon

Reputation: 15063

Since you're grouping by the medal type, it'll only select one of each type. If you want a list of all the medals a user got, why are you grouping by the medal type?

Perhaps you're looking for the highest level of the medal by type? If that's the case, you might want to specify that.

Upvotes: 0

Caimen
Caimen

Reputation: 2619

It's not showing 3 and 4 because you are grouping it by medal type. So 3 and 4 and the same type as 1 and 2. If you take group by off it will show every medal.

If you want this group by but only want 3 and 4. Then then you simply need to 

order by m.medal_type descending

Upvotes: 0

JJ.
JJ.

Reputation: 5475

You have "GROUP BY medal_type". Since your medals table only has values of 1,2 for this column, you won't get 3 & 4 in your results.

Upvotes: 0

Related Questions