I have two tables, one stores the users, the other stores the users' email addresses.

  • table users: (userId, username, etc)
  • table userEmail: (emailId, userId, email)

I would like to do a query that allows me to fetch the latest email address along with the user record.
I'm basically looking for a query that says

FIRST ORDER BY userEmail.emailId DESC 
THEN GROUP BY userEmail.userId

This can be done with:

SELECT 
  users.userId
, users.username
, (
     SELECT 
       userEmail.email
     FROM userEmail
     WHERE userEmail.userId = users.userId
     ORDER BY userEmail.emailId DESC
     LIMIT 1
  ) AS email
FROM users
ORDER BY users.username;

But this does a subquery for every row and is very inefficient. (It is faster to do 2 separate queries and 'join' them together in my program logic).


The intuitive query to write for what I want would be:

SELECT 
  users.userId
, users.username
, userEmail.email
FROM users
LEFT JOIN userEmail USING(userId)
GROUP BY users.userId
ORDER BY 
  userEmail.emailId
, users.username;

But, this does not function as I would like. (The GROUP BY is performed before the sorting, so the ORDER BY userEmail.emailId has nothing to do).


So my question is:
Is it possible to write the first query without making use of the subqueries?


I've searched and read the other questions on stackoverflow, but none seems to answer the question about this query pattern.

sql mysql
Edit
Report