0

I already visited MySQL order by before group by for Single Group By it's working fine. I have issue with 2 group by columns.

I have below table named "messages". Messages are comments of User's Posts

id | posts_id | from_user_id | to_user_id | message_description | created_at
-----------------------------------------------------------------------------
1      1           1              2             test1             2016-09-06 10:00:00
2      1           1              2             test2             2016-09-06 11:00:00
3      1           4              2             test3             2016-09-06 09:00:00
4      1           4              2             test4             2016-09-06 15:00:00
5      2           1              2             test1             2016-09-06 10:00:00
6      2           1              2             test2             2016-09-06 11:00:00
7      2           4              2             test3             2016-09-06 09:00:00
8      2           4              2             test4             2016-09-06 15:00:00

Query's Result Output Should Be

id | posts_id | from_user_id | to_user_id | message_description | created_at
-----------------------------------------------------------------------------
2      1           1              2             test2             2016-09-06 11:00:00
4      1           4              2             test4             2016-09-06 15:00:00
6      2           1              2             test2             2016-09-06 11:00:00
8      2           4              2             test4             2016-09-06 15:00:00

What i'm trying to retrieve post wise & user wise their latest messages.

I gave tried below, but it's not giving result in correct order.

SELECT *
FROM messages
WHERE to_user_id = '2'
GROUP BY posts_id DESC,
         from_user_id
ORDER BY id DESC
Community
  • 1
  • 1
Jackson
  • 1,426
  • 3
  • 27
  • 60
  • @Strawberry i already visited that post. But it's for single group by only, i have to use 2 group by columns in that way not able to get answer – Jackson Sep 06 '16 at 07:05
  • 1
    Hmm. I don't think this is a group by/ order by question at all since the requirement seems to be to get the last entry per from_user_id. Try this SELECT t1.* FROM messages t1 WHERE t1.id = (select max(id) from messages t2 where t2.posts_id = t1.posts_id and t2.from_user_id = t1.from_user_id) – P.Salmon Sep 06 '16 at 07:38
  • You have 3 users, but you only seem to want to return messages relating to one of them (user 2) – Strawberry Sep 06 '16 at 08:33
  • Many many thanks @P.Salmon This is the answer i'm looking for. Solved :) – Jackson Sep 06 '16 at 08:39
  • @Strawberry still it's duplicate?? – Jackson Sep 06 '16 at 09:00
  • I'm not yet convinced that it's not a duplicate. P.Salmon has simply substituted a correlated subquery for an uncorellated one. – Strawberry Sep 06 '16 at 09:03
  • Your result set is 'user wise' only. – Strawberry Sep 06 '16 at 09:34

1 Answers1

1

This problem is identical to thousands of others posted on SO. An optimal solution, as discussed in the manual, is an uncorellated subquery, e.g.: 

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT MAX(id) id 
         FROM my_table 
        WHERE to_user_id = 2 -- OPTIONAL
        GROUP 
           BY from_user_id
     ) y 
    ON y.id = x.id;
Strawberry
  • 33,750
  • 13
  • 40
  • 57