CODEWITHSUNDEEP
mysqllaravel
Ben
Ben

Reputation: 5777

Order by not working as expected after using group by

I am attempting to get a list of messages, by grouping the from_user_id and to_user_id so they look like the screenshot below. Only, my problem is they do not seem to be ordering by the most recent.

Here's a screenshot showing how they look:

enter image description here

Query: select 

select 
concat(to_user_id, from_user_id) as group_by,
pm.*
from personal_messages pm
where (to_user_id = 1265) or (from_user_id = 1265)
group by group_by
order by id desc

Table Structure:

enter image description here

Upvotes: 0

Views: 3428

Answers (2)

Danielle Cox
Danielle Cox

Reputation: 88

Check out answer MySQL "Group By" and "Order By"

The problem is the the group by has requirements for order that are trumping order_by. There is not a 100% solution, but this link will give you something that should work for you.

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You need to add the date value to the query:

select concat(to_user_id, from_user_id) as group_by,
       max(updated_at) as max_updated_at
from personal_messages pm
where (to_user_id = 1265) or (from_user_id = 1265)
group by group_by
order by max_updated_at desc

The date value could be created_at or updated_at, this depends on your actual requirement. You have to use an aggregate function like MAX though, because neither of these fields appears in the GROUP BY clause.

Upvotes: 3

Related Questions