Harrison Oziegbe Harrison Oziegbe - 1 month ago 16
MySQL Question

Query database for most current message per receiver_id

Here is example of my mysql query problem:

Table name: messaging
Id ad_id sender_id receiver_id message
1 2 5 1 message1
2 2 5 1 message2
3 2 5 1 message3
4 8 7 3 message4
5 2 4 2 message5


Now i would like to run a mysql query which will output results in following format:

Id ad_id sender_id receiver_id message
3 2 5 1 message3
4 8 7 3 message4
5 2 4 2 message5


Note: multiple rows with the same value in ad_id, sender_id and receiver_id should output only the last row.

I would appreciate any assistance to achieve similar output. Thanks in advance.

Answer

You should use GROUP BY with the 3 columns you want to agroup and then use the MAX to get the last inserted row of each group (assuming the Id is auto-increment)

SELECT MAX(Id) as Id, ad_id, sender_id, receiver_id FROM messaging GROUP BY ad_id, sender_id, receiver_id;