CODEWITHSUNDEEP
phpmysql
frsechet
frsechet

Reputation: 790

MySQL GROUP BY incoherence

I'm having an issue with a query, and more specifically the GROUP BY function.

The query works perfectly and returns the right results in the right order, but when I try to group by user ID, it messes up everything.

SELECT u.ID, GREATEST( IF(c.climb_length >= 1, r.cotation_1, 0)
, IF(c.climb_length >= 2, r.cotation_2, 0)
, IF(c.climb_length >= 3, r.cotation_3, 0)
, IF(c.climb_length >= 4, r.cotation_4, 0)
, IF(c.climb_length >= 5, r.cotation_5, 0)
, IF(c.climb_length >= 6, r.cotation_6, 0)
, IF(c.climb_length >= 7, r.cotation_7, 0)
, IF(c.climb_length >= 8, r.cotation_8, 0)
, IF(c.climb_length >= 9, r.cotation_9, 0)
, IF(c.climb_length >= 10, r.cotation_10, 0)
) AS vmax
FROM users u
INNER JOIN climbs c
ON c.user_id = u.ID
INNER JOIN routes r
ON c.route_id = r.ID
GROUP BY vmax, u.ID
ORDER BY vmax DESC

This returns something like that, which is close to what I want, except for the duplicate u.IDs (usernames displayed for readability):

The problem is, I only want the highest vmax per u.ID - each u.ID appearing only once. So if I do the same request but with only GROUP BY u.ID, I get this, which makes no sense:

Here, the vmax values are wrong, hence the order is wrong. I don't even understand where those values come from: the vmax value for each u.ID does not even match the last inserted vmax of each user as calculated by the query (I can show that if I add c.ID in the SELECT part), nor the first one. They do belong to each user, but they are all over the place chronologically.

I have tried many things, researched and read a lot (for example this link seemed promising), but it doesn't make much sense to me that this last bit above does not work. Does anyone see where I messed up?

Thanks!

Upvotes: 0

Views: 52

Answers (1)

Rimas
Rimas

Reputation: 6024

Use aggregate function MAX and group by u.ID only:

SELECT u.ID, MAX(GREATEST( IF(c.climb_length >= 1, r.cotation_1, 0)
, IF(c.climb_length >= 2, r.cotation_2, 0)
, IF(c.climb_length >= 3, r.cotation_3, 0)
, IF(c.climb_length >= 4, r.cotation_4, 0)
, IF(c.climb_length >= 5, r.cotation_5, 0)
, IF(c.climb_length >= 6, r.cotation_6, 0)
, IF(c.climb_length >= 7, r.cotation_7, 0)
, IF(c.climb_length >= 8, r.cotation_8, 0)
, IF(c.climb_length >= 9, r.cotation_9, 0)
, IF(c.climb_length >= 10, r.cotation_10, 0)
)) AS vmax
FROM users u
INNER JOIN climbs c
ON c.user_id = u.ID
INNER JOIN routes r
ON c.route_id = r.ID
GROUP BY u.ID
ORDER BY vmax DESC

Upvotes: 2

Related Questions