MySQL select GROUP BY order

Question

I have a mysql statement

SELECT * 
FROM tbl_messages 
WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' 
GROUP BY from_user_id 
ORDER BY date_sent DESC

and it is producing the correct results however they are not in the correct order.

The grouping works well but it record displayed in the group is the first recorded entered into the DB but I would like the latest record to be displayed in each group.

Is there a way to have the latest record displayed for each group?

2011-12-19 12:16:25 This is the first message
2011-12-19 12:18:20 This is the second message
2011-12-19 12:43:04 This is the third message

The group shows 'This is the first message' where I would like 'This is the third message' as that is the most recent record/message.

Cheers

If you need the two columns only (e.g. ID and its latest timestamp), this might work: http://stackoverflow.com/a/4448536/722036. It's **faster** than using sub-queries on a huge table with millions of rows. — ᴍᴇʜᴏᴠ, Jun 26 '16 at 11:38

ypercubeᵀᴹ · Accepted Answer · 2011-12-19T02:00:46.117

10

This may work (but not guaranteed):

SELECT * 
FROM
  ( SELECT *
    FROM tbl_messages 
    WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' 
    ORDER BY date_sent DESC
  ) tmp
GROUP BY from_user_id 
ORDER BY date_sent DESC

This should work:

SELECT t.* 
FROM 
    tbl_messages AS t
  JOIN
    ( SELECT from_user_id 
           , MAX(date_sent) AS max_date_sent
      FROM tbl_messages 
      WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' 
      GROUP BY from_user_id 
    ) AS tg
    ON  (tg.from_user_id, tg.max_date_sent) = (t.from_user_id, t.date_sent)
ORDER BY t.date_sent DESC

edited Dec 19 '11 at 02:00

answered Dec 19 '11 at 01:54

ypercubeᵀᴹ

113,259
19
174
235

This seems to be working. A slight modification needed (add tbl_messages before the GROUP BY) but looks good. Thanks! – puks1978 Dec 19 '11 at 02:02
Thanks save my day, in school I learned more about subquerys... I did not remember this greats querys ... my teacher ... old memories – Dec 17 '15 at 14:41

score 8 · Answer 2 · answered Apr 30 '13 at 19:56

8

Do a GROUP BY after the ORDER BY by wrapping your query with the GROUP BY like this:

SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from

answered Apr 30 '13 at 19:56

11101101b

7,679
2
42
52

Even if I put the ASC in the query "t" its taking DESC. – Mohammed Abrar Ahmed Nov 01 '17 at 16:04

score 2 · Answer 3 · answered Dec 19 '11 at 01:53

2

If your messages table has a primary key that is auto-increment, and all messages are by nature highest number is the most recent date... However, since I don't KNOW that, I am going based on the MAX( date_sent ) instead of max( SomeIDKey ), but the principle is the same.

select
      tm2.*
   from
      ( select tm1.from_user_id, 
               max( tm1.date_sent ) LatestMsgDate
           from tbl_messages tm1
           group by tm1.from_user_id ) MaxPerUser

      left join tbl_messages tm2
         on MaxPerUser.From_User_ID = tm2.From_User_ID
        AND MaxPerUser.LatestMsgDat = tm2.Date_Sent

   order by
      date_sent DESC

answered Dec 19 '11 at 01:53

DRapp

47,638
12
72
142

how would I add ORDER BY in MaxPerUser and GROUP BY at the end of the query. – Mohammed Abrar Ahmed Nov 01 '17 at 16:03
1

@MohammedAbrarAhmed, no order by applicable to the inner MaxPerUser query. However, if you wanted your outer results based on the most recent message date, you could ORDER BY MaxPerUser.LatestMsgDate -- if that is what you are considering to have the most recent messages float to the top. – DRapp Nov 02 '17 at 01:20

score -1 · Answer 4 · answered Dec 19 '11 at 01:38

-1

Do you mean something like this:

SELECT * FROM tbl_messages WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' GROUP BY from_user_id ORDER BY from_user_id, date_sent DESC

answered Dec 19 '11 at 01:38

Sudhir Bastakoti

99,167
15
158
162

MySQL select GROUP BY order

4 Answers4