FIRST ORDER BY ... THEN GROUP BY ...

Answer 1

If this is a query you do often, I recommend optimizing your tables to handle this.

I suggest adding an emailId column to the users table. When a user changes their email address, or sets an older email address as the primary email address, update the user's row in the users table to indicate the current emailId

Once you modify your code to do this update, you can go back and update your older data to set emailId for all users.

Alternatively, you can add an email column to the users table, so you don't have to do a join to get a user's current email address.

Answer 2

But this does a subquery for every row and is very inefficient

Firstly, do you have a query plan / timings that demonstrate this? The way you've done it (with the subselect) is pretty much the 'intuitive' way to do it. Many DBMS (though I'm not sure about MySQL) have optimisations for this case, and will have a way to execute the query only once.

Alternatively, you should be able to create a subtable with ONLY (user id, latest email id) tuples and JOIN onto that:

SELECT 
  users.userId
, users.username
, userEmail.email
FROM users
INNER JOIN 
      (SELECT userId, MAX(emailId) AS latestEmailId
       FROM userEmail GROUP BY userId)
      AS latestEmails
      ON (users.userId = latestEmails.userId)
INNER JOIN userEmail ON
      (latestEmails.latestEmailId = userEmail.emailId)
ORDER BY users.username;