php - Group by Latest entry and filter out with additional criterias -

_table:

id    sender    receiver        message  1      14        16            1st message 14 16  2      16        14            1st message 16 14  3      16        14            2nd message 16 14  4      14        16            2nd message 14 16  5      15        14            1st message 15 14  6      15        14            2nd message 15 14  7      14        16            3rd message 14 16  8      14        16            4th message 14 16  9      14        15            1st message 14 15 10      14        15            2nd message 14 15 

now, trying here group messages 1 user (as receiver), problem i want latest entry regardless of sent message.

_{attempt 1:}

select c2. * (  select max( id ) `id` tbl_msg group `sender` )c1 inner join tbl_msg c2 on c1.id = c2.id `receiver` =14 group `sender` 

_result:

id    sender    receiver        message  6      15        14            2nd message 15 14  3      16        14            2nd message 16 14 

here result each last message sent user 14. won't include message sent by user 14.

again, can't use addtional group by on receiver, because include last entry sent by user 14.

_{expected output:}

id    sender    receiver        message 10      14        15            2nd message 14 15  8      14        16            4th message 14 16 

now in above, sender in both entries 14, can user.

in simple words:,

i want retrieve last message in conversation between , b, regardless of said it.

is using group by here wrong approach?

n.b. questions below similiar question with exception deal 1 criteria. here, have 2 (i.e. user can either sender or receiver). part stuck at.

retrieving last record in each group

mysql - control row returned group by

returning 'last' row of each 'group by' in mysql

1st row in group vs. last row

try this,

select  *    tablename   (least(sender, receiver),greatest(sender, receiver), id)          in (                 select  least(sender, receiver) x,                         greatest(sender, receiver) y,                         max(id) max_id                    tablename                 group   x, y             ) 

• sqlfiddle demo

output

╔════╦════════╦══════════╦═══════════════════════════╗ ║ id ║ sender ║ receiver ║          message          ║ ╠════╬════════╬══════════╬═══════════════════════════╣ ║  8 ║     14 ║       16 ║ 4th message 14 16 ║ ║ 10 ║     14 ║       15 ║ 2nd message 14 15 ║ ╚════╩════════╩══════════╩═══════════════════════════╝