Edit Question - KnowledgeHub

Title Body

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.

Tags (comma-separated) Cancel