Dynamic column, one value, GROUP BY and ORDER BY

Question

I need to develop a private messages system between users and here is what I want to do :

I have a table like the following :

id | from_user_id | to_user_id | message
1  | 1            | 2          | Hey n°1 !    <-- Me with user n°2
2  | 2            | 1          | Hey n°2 !    <-- Me with user n°2
4  | 1            | 3          | Hey n°3 !    <-- Me with user n°3
3  | 3            | 2          | Hey n°4 !

We suppose that I'm the user n°1, I want to get a list of my last messages "group by users" and ordered by id, as discussions :

id | with_user_id | message
4  | 3            | Hey n°3 !
2  | 2            | Hey n°2 !

I've tried a request like :

SELECT id, message,
       (CASE WHEN from_user_id = 1 THEN to_user_id ELSE from_user_id END) AS with_user_id
FROM privates
WHERE from_user_id = 1 OR to_user_id = 1
GROUP BY with_user_id
ORDER BY id DESC

But I'm getting this :

id | with_user_id | message
4  | 3            | Hey n°3 !
1  | 2            | Hey n°1 !

So the problem is that it selects the first message with user n°1 and not the last.

I deleted my answer because I made a mistake with my syntax, give me some time and I will post a working answer. — , Nov 14 '13 at 16:29
You answer was right ! I've just added "ORDER BY SQ.id DESC" at the end and it works ! — Maxime R., Nov 14 '13 at 16:30
Oh, you must be using MySQL then as MSSQL doesn't allow an order by in the subquery. Well if it worked I will repost the answer :) — , Nov 14 '13 at 16:34

score 1 · Accepted Answer · edited May 23 '17 at 12:28

Note: This will not work in MSSQL as OrderBy clauses are not allowed in sub queries. you can use a method similar to the answer in this question if you are using MSSQL.

You need to apply the order by before the group by, because the group by statement is grouping messages 1 and 2 together and picking the first. Try something like this:

Select SQ.*
FROM (
    SELECT id, message,
       (CASE WHEN from_user_id = 1 THEN to_user_id ELSE from_user_id END) AS with_user_id
    FROM privates
    WHERE from_user_id = 1 OR to_user_id = 1
    ORDER BY id DESC
) AS SQ
GROUP BY SQ.with_user_id

Ordering in this way will get only the most recent message for a given conversation.

score 0 · Answer 2 · answered Nov 14 '13 at 16:34

look like you need all messages for creating dialog with user, try following select

select id, from_user_id, to_user_id, message,
  from privates
 where from_user_id = 1
    or to_user_id = 1
 order by case
              when from_user_id = 1 then
               to_user_id
              else
               from_user_id
          end,
          id desc

Dynamic column, one value, GROUP BY and ORDER BY

2 Answers2