SQL get last messages from/to certain user

Question

I'm trying to make SQL query from table, - get last messages from all pairs of users with user 36 as sender or recipient, and join them with users table to get names. I've managed to create something like this, but still want to ask if there is more simple | efficient solution. Mysql version - 5.5.31

    table: messages
    fields: 
    sender_user_id, recipient_user_id, date, text

query:

SELECT 
    *
FROM
    (SELECT 
        (CASE sender_user_id > recipient_user_id
                WHEN true THEN CONCAT(recipient_user_id, '|', sender_user_id)
                WHEN false THEN CONCAT(sender_user_id, '|', recipient_user_id)
            END) as hash,
            sender_user_id,
            recipient_user_id,
            date,
            text,
            u.first_name
    FROM
        fed_messages
    LEFT JOIN fed_users as u ON ((fed_messages.sender_user_id = 36 AND fed_messages.recipient_user_id = u.id) OR (fed_messages.recipient_user_id = 36 AND fed_messages.sender_user_id = u.id))
    WHERE
        sender_user_id = 36 OR recipient_user_id = 36
    ORDER BY date DESC) as main
GROUP BY hash;

Thanks. Upd. Data from sample messages table:

mysql> SELECT id, sender_user_id, recipient_user_id, text, date FROM federation.fed_messages WHERE id > 257;

-----+----------------+-------------------+-----------------+---------------------+
| id  | sender_user_id | recipient_user_id | text            | date                |
+-----+----------------+-------------------+-----------------+---------------------+
| 258 |             40 |                36 | and one more    | 2013-06-06 10:57:17 |
| 259 |             36 |                38 | another message | 2013-06-06 11:03:49 |
| 260 |             38 |                36 | some mes        | 2013-06-06 12:29:33 |
| 261 |             38 |                36 | message         | 2013-06-06 12:29:53 |
| 262 |             36 |                38 | message         | 2013-06-06 12:47:26 |
| 263 |             36 |                40 | some message    | 2013-06-10 16:22:46 |

The result should be with ids - 262, 263

Answer 1

I'm using SQL Server 2008, you don't say which database you are using.

From the information you have supplied your query seems overly complex for the output you require. Here's a simple query to get all the messages involving user 36:

SELECT  
       sender.msg_user_name AS sender_user_name
       ,recipient.msg_user_name AS recipient_user_name
       ,msg_date
       ,msg_text

FROM    
       dbo.Fed_Messages 

       INNER JOIN dbo.Fed_User AS sender 
       ON sender.msg_user_id = sender_user_id

       INNER JOIN dbo.Fed_User AS recipient 
       ON recipient.msg_user_id = recipient_user_id

WHERE   
       sender_user_id = 36
       OR recipient_user_id = 36

ORDER BY
       msg_date DESC

I've had to change some field names as in SQL Server some of the names you have chosen are reserved words.

SQL Fiddle: http://sqlfiddle.com/#!3/b8e88/1

EDIT: Now you've added some more information, and shown there is an id field on the message table, you could use something like this (note: I have SQL Server so you will probably have to change the query for MySQL):

SELECT  sender.msg_user_name AS sender_user_name
       ,recipient.msg_user_name AS recipient_user_name
       ,msg_date
       ,msg_text
FROM    dbo.Fed_Messages
        INNER JOIN dbo.Fed_User AS sender ON sender.msg_user_id = sender_user_id
        INNER JOIN dbo.Fed_User AS recipient ON recipient.msg_user_id = recipient_user_id
        INNER JOIN ( SELECT MAX(id) AS most_recent_message_id
                     FROM   dbo.Fed_Messages
                     GROUP BY CASE WHEN sender_user_id > recipient_user_id
                                   THEN recipient_user_id
                                   ELSE sender_user_id
                              END -- low_id
                           ,CASE WHEN sender_user_id < recipient_user_id
                                 THEN recipient_user_id
                                 ELSE sender_user_id
                            END -- high_id
                   ) T ON T.most_recent_message_id = dbo.Fed_Messages.id
WHERE   sender_user_id = 36
        OR recipient_user_id = 36
ORDER BY msg_date DESC

The SELECT in the FROM part of the query finds the most recent message (based on the id, I'm assuming it's an auto incrementing number) for each ordered pair of sender/recipient user id's. The result of that is rejoined to the Fed_Messages table to ensure we get the names for sender/receiver correct.

Updated SQL Fiddle: http://sqlfiddle.com/#!3/1f07a/2