How to select all conversation by users which also display order by last date

Question

The below query I used to get all user IDs, who message me OR message by me. It's working well. But now I want to show the last user at the top. How to do it please?

SELECT s.user1,s.user2 FROM(
SELECT CASE WHEN t.mfrom > t.mto THEN t.mfrom ELSE t.mto END as user1, 
CASE WHEN t.mfrom < t.mto THEN t.mfrom ELSE t.mto END as user2 
FROM messager t WHERE t.mfrom = '$ownid' OR t.mto='$ownid' ORDER BY t.date DESC) s GROUP BY s.user1,s.user2

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 14 '20 at 21:06

score 0 · Accepted Answer · answered Aug 14 '20 at 16:57

I think you want:

SELECT (CASE WHEN t.mfrom > t.mto THEN t.mfrom ELSE t.mto END) as user1, 
       (CASE WHEN t.mfrom < t.mto THEN t.mfrom ELSE t.to END) as user2 
FROM messager m
WHERE ? IN (t.mfrom, t.mto)
GROUP BY user1, user2
ORDER BY MAX(t.date) DESC;

Note:

You don't need a subquery.
Use parameters! Don't munge query strings with literal values.
IN is simpler than OR.
The solution is the ORDER BY with the MAX().

How to select all conversation by users which also display order by last date

1 Answers1