SQL receive recent messages from / to a specific user

Question

I am trying to make an SQL query from a table - to receive the latest messages from all user pairs with user 36 as the sender or recipient and join them using the users table to get the names. I managed to create something like this, but still want to ask if there are simpler ones | effective solution. Mysql Version - 5.5.31

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

request:

 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 the table of sample messages:

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 identifiers - 262, 263

Answer 1

I am using SQL Server 2008, you are not saying which database you are using.

From the information you provided, your request seems too complex for the desired result. Here's a simple query to get all 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 had to change the field names, as in SQL Server, some of the names you selected are reserved words.

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

EDIT: Now you have added additional information and shown that there is an id field in the message table, you can use something like this (note: I have SQL Server, so you 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 

SELECT in the FROM part of the query finds the most recent message (based on id , I assume this number increases automatically) for each ordered pair of sender / receiver user IDs. The result of this returns to the Fed_Messages table to verify that the names for the sender / receiver are correct.

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