0

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.

RST
  • 3,899
  • 2
  • 20
  • 33
Harrison O
  • 1,119
  • 15
  • 20

2 Answers2

0

I suggest shortlisting the correct Ids before extracting the data, especially if you have more columns.

select Id,  ad_id,  sender_id,  receiver_id,    message 
from messaging
where id in (select max(id)
        from messaging
        group by ad_id, sender_id,  receiver_id)
UV.
  • 492
  • 6
  • 9
-1

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;
leoap
  • 1,684
  • 3
  • 24
  • 32
  • I really appreciate your help but i just tried this code but i got the following error: Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given... – Harrison O Nov 02 '16 at 23:18
  • execute my sql code directly on your database (in phpmyadmin if you use it). It works the way you want. The warning that you are receiving its another problem, related to your php code. – leoap Nov 02 '16 at 23:24
  • Thanks, i really appreciate. I was able to detect the problem within the phpmyadmin but still trying resolve it within my php code. – Harrison O Nov 02 '16 at 23:39
  • :) create another question about your php code. and mark this one as resolved – leoap Nov 02 '16 at 23:42
  • I fixed my php code, but i tried adding WHERE clause on the query but i am getting an error. Please see here: SELECT MAX(id) AS id, ad_id, sender_id, reciever_id, message, status, date_sent FROM messaging WHERE reciever_id='$userID' GROUP BY ad_id, sender_id, reciever_id – Harrison O Nov 02 '16 at 23:51
  • you need to post the error message too – leoap Nov 03 '16 at 00:05
  • Thanks, i really appreciate. All set. – Harrison O Nov 03 '16 at 00:11