15
Order by not working as expected after using the group by
source link: https://www.codesd.com/item/order-by-not-working-as-expected-after-using-the-group-by.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Order by not working as expected after using the group by
advertisements
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:
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:
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK