Count where and group by but show the latest id of each group

I have chat_notif table like this :

What i need is to get count but with group by order_id where sent_to is 2:

SELECT *, count(id) as count FROM chat_notif WHERE sent_to=2 GROUP BY order_id

From the table above, the count is right. But what i need is to get the last id of each group so it should be like this :

How do i do it then?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

We can find the last values with MAX and use that to aggregate the values into the query.

If we want to include other fields along with the count() function we need to either include them in the GROUP BY or use another aggregate function such as SUM or AVERAGE.

Luckily, you mention you only want the last id of each group. MAX works well for this purpose and counts as an aggregate function, so we’re in luck.

Note, this assumes the fields “id” and “chat_id” are numeric types such as integers so that they work with the MAX function. You may run into issues with other (non-numeric) data types.

SELECT MAX(id) as id, order_id, MAX(chat_id) as chat_id, sent_to, count(id) as count 
FROM chat_notif 
WHERE sent_to=2 
GROUP BY order_id;
    
+------+-----------+---------+---------+-------+
| id   | order_id  | chat_id | sent_to | count |
+------+-----------+---------+---------+-------+
|    9 | order-001 |       9 |       2 |     4 |
|    8 | order-003 |       8 |       2 |     3 |
+------+-----------+---------+---------+-------+
2 rows in set (0.00 sec)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0