Mysql Order By Total Rows Of User In Another Table

Suppose, I want to show a list of users ordering by the most number of messages they have sent. I have 2 tables: Users and Messages I have 10 users User A sent 20 messages (have 20

Solution 1:

SELECTuser, COUNT(*) FROM messages GROUPBYuserORDERBYcount(*) DESC;

Solution 2:

if you want to print the names join users table,

select user_name, count(*) from users innerjoin messages m on users.userid=m.messageid groupby userid orderbycount(*) desc;

Solution 3:

You can sort using an alias:

SELECTuser, COUNT(1) as cnt
FROM Messages 
GROUPBYuserORDERBY cnt DESC;

or position:

SELECTuser, COUNT(1) as cnt
FROM Messages 
GROUPBYuserORDERBY2DESC;