Q:

Facebook like messaging system - Sort by last reply

I am building a messaging system similar to facebook's (where it displays messages as threads).
My current table design is:
CREATE TABLE IF NOT EXISTS messages ( 
  mid int(11) NOT NULL auto_increment,
 subject text NOT NULL,
  message text NOT NULL,
  fromid varchar(255) NOT NULL default '',
  toid varchar(255) NOT NULL default '',
  status varchar(255) NOT NULL default '',
  date varchar(255) NOT NULL default '',
  time varchar(255) NOT NULL,
  PRIMARY KEY  (mid)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2825 ;`

I am retrieving results with this select statement:
SELECT 
  IF(messages.toid = '$uid' OR messages.toid = '$uid', messages.fromid, messages.toid) friend1,
messages.message, messages.fromid, messages.toid, messages.date, messages.status, messages.time
FROM messages 
WHERE (messages.toid='$uid' OR messages.fromid='$uid') 
  AND messages.status!='2' 
GROUP BY friend1 ASC 
ORDER BY messages.time DESC, messages.mid DESC

This gives me the right results except for it displays the first post from a thread, I would like for it to display the most recent post in a thread.
What am I doing wrong?

A:

This is a nice article directly related to your question: http://kristiannielsen.livejournal.com/6745.html
Taking a wild stab at your specific problem, your query would probably need to look something like below (untested!):
SELECT 
  IF(derived_messages.toid = '$uid', derived_messages.fromid, 
    derived_messages.toid) friend1,
  derived_messages.message, derived_messages.fromid, derived_messages.toid,
  derived_messages.date, derived_messages.status, derived_messages.time
FROM 
  (SELECT * 
  FROM messages
  ORDER BY time desc) derived_messages
WHERE (derived_messages.toid='$uid' OR derived_messages.fromid='$uid') 
  AND derived_messages.status!='2' 
GROUP BY friend1 ASC 
ORDER BY derived_messages.time DESC, derived_messages.mid DESC

Btw this clause looked fishy to me (in your original post):
IF(messages.toid = '$uid' OR messages.toid = '$uid',
  messages.fromid, messages.toid) friend1

What's the "OR" doing between identical conditions? You can probably skip the second condition, I think.

