mysql查询返回每个结果的第一个匹配项

Question

I have a chat system that works fine, but the message list I've just realised pulls every message per person then displays the last result, which obviously is a massive waste of resources and stress on the server to return only one result per user.

This is the query to retrieve the message list, and afterwards there is a while loop that cleans the array to show only the latest message per user, I know there is a much better way to do this using JOIN but I can't get my head around it.

$result = dbconstruct("SELECT messages.id, messages.msgfrom, messages.msg, 
messages.active, messages.replied, users.username, users.online, users.admin, 
users.imagename FROM messages, users WHERE msgto='$_SESSION[id]' 
AND users.id=msgfrom ORDER by messages.id DESC");

I have looked through other answers, but as stated, finding it quite hard to grasp the join techniques.

update

I don't think I've explained what I need very well. Currently I run the above query, return about 2000 results and then use this to filter each users message to a single message.

$clean = array(); while($result->fetch_assoc()) { if(in_array($result[msgfrom],     
$clean)) { }
else { //print message }

$usercheck = array_push($clean, $result[msgfrom]); }

This displays the message list fine and it shows only the last message from each user, but is a very bad way to do so. There must be a way to do this inside the SQL query.

Answer 1

Assuming that message.id is auto_incremented, the MAX is the latest. Otherwise if you have a column as a timestamp you can use that and apply the same principle.

I think the most accurate form of replicating what you are trying in your php is to select only the latest message from someone (subquery) and join it with your desired output query. I think it will return what you expect :

SELECT m.id,
  m.msgfrom,
  m.msg,
  m.active,
  m.replied,
  u.username,
  u.online,
  u.admin,
  u.imagename
FROM messages m 
INNER JOIN users u ON u.users.id = m.msgfrom
INNER JOIN (SELECT MAX(id) as id, msgfrom
            FROM messages
            GROUP BY msgfrom
           ) m2
    ON m2.id = m.id AND m2.msgfrom = m.msgfrom
WHERE messages.msgto = '$_SESSION[id]'