There is a table with a list of messages on the site, in which there are two columns: Author and Recipient. It is necessary to select from the table all the latest messages for a given user, which may include both the Author and the Recipient. Those. if we find 2 entries, in the first the user is the author, and in the second with the same interlocutor is the recipient, then we need only the last entry.

How to do it better?

1 answer 1

In MySQL, you can do this:

SELECT MSGs.* FROM MSGs JOIN ( SELECT CASE WHEN user_from < user_to THEN user_from ELSE user_to END id1, CASE WHEN user_from > user_to THEN user_from ELSE user_to END id2, MAX(send_time) max_time FROM MSGs --автор или получатель WHERE @UserId = user_from OR @UserId = user_to --группируем по двум столбцам GROUP BY CASE WHEN user_from < user_to THEN user_from ELSE user_to END, CASE WHEN user_from > user_to THEN user_from ELSE user_to END )T --соединяем по автору/получателю и времени сообщения ON send_time = max_time AND CASE WHEN user_from < user_to THEN user_from ELSE user_to END = id1 AND CASE WHEN user_from > user_to THEN user_from ELSE user_to END = id2 WHERE @UserId = user_from OR @UserId = user_to

In MS SQL, Oracle, PG SQL would be easier:

 SELECT user_from, user_to, send_time FROM( SELECT MSGs.*, ROW_NUMBER()OVER(PARTITION BY CASE WHEN user_from < user_to THEN user_from ELSE user_to END, CASE WHEN user_from > user_to THEN user_from ELSE user_to END ORDER BY send_time DESC ) N FROM MSGs --автор или получатель WHERE @UserId = user_from OR @UserId = user_to )T WHERE N=1