How do I select only the most recent record based on a userid that can be in either of two columns?

I have a messages table that is structured somewhat like this:

from  |  to  |  date
-----------------------------------
1     |  3   |  2011-09-23 11:51:44
3     |  1   |  2011-09-23 11:56:29
3     |  2   |  2011-10-04 10:20:01
2     |  3   |  2011-10-05 07:48:00

I want to display a messages page on my website very similar to Facebook's messages page, which shows a list of the people with which the user has a conversation. Regardless of the depth of the conversation, the page only shows each person once, along with the most recent message in that conversation with that particular person, whether it was sent or received.

The part that stumps me is that the most recent message can be either sent or received, which means that the user's ID number can be in either the from or to column. I'm not sure how to test against both columns the way I need to.

I'm still learning how to write more complex MySQL queries, and while I feel like this is a simple case of using OR with subqueries, I can't seem to get it right.

SOLUTION Turns out it wasn't really a very simple case at all. Widor took some time to help me out with this, and the following query finally seems to do the job. It hasn't been tested thoroughly, but so far it seems to work fine:

SELECT m.*
FROM   messages m
       JOIN (SELECT Max(x.id)      AS `id`,
                    x.userid,
                    x.partnerid,
                    Max(x.mostrecent) AS `mostrecent`
             FROM  (SELECT Max(id)     `id`,
                           `from`         AS `userid`,
                           `to`           AS `partnerid`,
                           Max(`created`) AS `mostrecent`
                    FROM   me开发者_StackOverflow社区ssages
                    GROUP  BY `from`,
                              `to`
                    UNION
                    SELECT Max(id)     `id`,
                           `to`           AS `userid`,
                           `from`         AS `partnerid`,
                           Max(`created`) AS `mostrecent`
                    FROM   messages
                    GROUP  BY `to`,
                              `from`) AS `x`
             GROUP  BY x.`userid`,
                       x.`partnerid`) AS `y`
         ON y.id = m.id
WHERE  y.userid = $userid

RE-EDITED

My previous answer (as some other ones here) didn't take account of the case where you have more than 2 conversation 'partners', which isn't the case in your example data but I'm sure will be in real life.

So consider the case where you now have an extra record in the data:

1    |     4    | 2011-10-04 08:34:12

My revised query is as follows:

SELECT userid, partnerid, max(mostRecent) from (
    SELECT [from] as [userid], [to] as [partnerid], max([date]) as [mostrecent] FROM messages GROUP BY [from], [to]
    UNION 
    SELECT [to] as [userid], [from] as [partnerid],max([date]) as [mostrecent] FROM messages GROUP BY [to], [from]
) [x]
WHERE userid = ?
GROUP BY userid, partnerid

So, our inner UNION gives us a dataset containing the userid along with a partnerid, i.e. who they are chatting to. This could be created separately as a view if you wanted, to simplify the query.

The outer SELECT then retrieves one record for every 'partner' that the specified userid has had a chat with, along with the most recent date.

The Max() function achieves the most recent date, and the GROUP BY takes care of ensuring we bring back more then one record for each partner.

Sounds like you want something like:

SELECT MAX(date), * FROM messages WHERE to IN (
    SELECT DISTINCT to FROM messages WHERE from = :id
) GROUP BY to
UNION
SELECT MAX(date), * FROM messages WHERE from IN (
    SELECT DISTINCT from FROM messages WHERE to = :id
) GROUP BY from
ORDER BY 1

Where id is the parameter you're using for the user ID.

you can create a view and query it:

CREATE VIEW dataview AS 
(SELECT t1.from AS userid, t1.date FROM messages AS t1) 
UNION
(SELECT t2.to AS userid, t2.date FROM messages AS t2)

look at the result table, it should now be very easy to query what you want

I think you probably want something like this:

SELECT MAX(date)
FROM messages
WHERE from = $userid
   OR to   = $userid

You would replace $userid with the actual value, hopefully using prepared statements. :-)