Gijo Varghese Gijo Varghese - 1 month ago 9
MySQL Question

How to 'GROUPT BY' after 'ORDER BY' in MySQL

I'm using the following query to get a list of users having maximum 'speed'.

With group by:

SELECT users.email, speed.speed
FROM users INNER JOIN speed ON users.email=speed.email
GROUP BY users.email
ORDER BY speed.speed DESC LIMIT 15


enter image description here

How ever on running the query, the 'ORDER BY' is not working. It always show the result in the top.

If I remove 'GROUP BY', I got the order list. But I only want 1 row with same email ids. How can i apply group by on this?

Without Group By:

SELECT users.email, speed.speed
FROM users INNER JOIN speed ON users.email=speed.email
ORDER BY speed.speed DESC LIMIT 15


enter image description here

Answer

Without an aggregate function, GROUP BY will show you the grouped column with whichever value is selected last from non-grouped columns. You'll want to use an aggregate function on your speed.speed column if you want it sorted.

If you want each user with their maximum speed you'll want to do something like

SELECT users.email, MAX(speed.speed) FROM users INNER JOIN speed ON users.email=speed.email ORDER BY speed.speed DESC LIMIT 15

Or if you wanted the minimum

SELECT users.email, MIN(speed.speed) FROM users INNER JOIN speed ON users.email=speed.email ORDER BY speed.speed DESC LIMIT 15

Or Both

SELECT users.email, MAX(speed.speed) as maxspeed, MIN(speed.speed) as minspeed FROM users INNER JOIN speed ON users.email=speed.email ORDER BY speed.speed DESC LIMIT 15