0

Table:

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

Now, what I am trying to do here to is to group the messages for one user (as a receiver), but the problem is that I want the latest entry regardless of who sent the message.

Attempt 1:

SELECT c2. *
FROM (

SELECT max( id ) `id`
FROM tbl_msg
GROUP BY `sender`
)c1
INNER JOIN tbl_msg c2 ON c1.id = c2.id
WHERE `receiver` =14
GROUP BY `sender`

Result:

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

Here the result is that each last message is sent to user 14. It obviously won't include the message sent by user 14.

Again, I can't use an addtional GROUP BY on receiver, because then it would only include the last entry sent by user 14.

Expected output:

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

Now in the above, sender in both the entries are 14, but it can be any user.

In simple words:,

I want to retrieve the last message in a conversation between A and B, regardless of who said it.

Is using GROUP BY here a wrong approach?

N.B. The questions below are somewhat similiar to this question with the exception that they deal with only one criteria. But here, I have two (i.e. the user can be either a sender or a receiver). This is the part where I am stuck at.

Retrieving the last record in each group

MySQL - Control which row is returned by a group by

Returning the 'last' row of each 'group by' in MySQL

1st Row in Group By vs. Last Row

Community
  • 1
  • 1
itachi
  • 6,323
  • 3
  • 30
  • 40

1 Answers1

2

Try this,

SELECT  *
FROM    TableName
WHERE   (LEAST(sender, receiver),GREATEST(sender, receiver), id) 
        IN (
                SELECT  LEAST(sender, receiver) AS x,
                        GREATEST(sender, receiver) AS y,
                        MAX(id) AS max_ID
                FROM    TableName
                GROUP   BY x, y
            )

OUTPUT

╔════╦════════╦══════════╦═══════════════════════════╗
║ ID ║ SENDER ║ RECEIVER ║          MESSAGE          ║
╠════╬════════╬══════════╬═══════════════════════════╣
║  8 ║     14 ║       16 ║ 4th message from 14 to 16 ║
║ 10 ║     14 ║       15 ║ 2nd message from 14 to 15 ║
╚════╩════════╩══════════╩═══════════════════════════╝
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • exactly what i wanted. a little bit puzzled about the sql though. can you tell me what part does comparing the ids of `sender` and `receiver` is playing here? – itachi May 19 '13 at 07:54
  • it's on the subquery, see here http://sqlfiddle.com/#!2/1ad63/6 the purpose of the outer query is to be able to get all remaining columns in the table. – John Woo May 19 '13 at 07:58
  • one problem i am seeing here (just now discovered) is, it applies for datas for the least id (14 here). let's say i want to retreive the last messages of user15 against 14 and 16. or last message with respect to user 16 against 14 and 15. how should it be improvised? – itachi May 19 '13 at 08:07
  • actually it's not about the `LEAST()`. It's all because of the `ID`. Why don;t you have any dates between the conversation. using `ID`, *I guess it is AUTO_INCREMENTed*, is not reliable at all. – John Woo May 19 '13 at 08:12
  • yes. in one of the above links in the question, one has mentioned it. let's say if i use date field, then what should be the column type for this purpose two fields for `date` and `time`, `datetime` or `timestamp`? can you add the releveant query on the basis of that (date and time field) for above? – itachi May 19 '13 at 08:26
  • i prefer `datetime` then in the query replace `ID` to your `datetime` column. – John Woo May 19 '13 at 08:27