CODEWITHSUNDEEP
phpmysql
munjal
munjal

Reputation: 1404

got stuck in mysql query

I want to count the maximum number of messages that a single user has sent and received from another single user. I am not getting understand how to achieve this.

Example for 3 messages exchanged:
[1] John(1472) msg Jane
    Jane msg John(1472)
[2] John(1472) msg Jane
    Jane msg John(1472)
[3] John(1472) msg Jane

OR

[1] John(1472) msg Jane
    Jane msg John(1472)
[2] John(1472) msg Jane
    Jane msg John(1472)
[3] John(1472) msg Jane
    Jane msg John(1472)

OR

[1] John(1472) msg Jane
    John(1472) msg Jane
    John(1472) msg Jane
    Jane msg John(1472)
[2] John(1472) msg Jane
    John(1472) msg Jane
    Jane msg John(1472)
    Jane msg John(1472)
[3] John(1472) msg Jane
    Jane msg John(1472)

All above examples will give 3 messages exchanged between these two users. My Table structure is like this : http://sqlfiddle.com/#!9/f84f17/1

Everytime a user sends a message to another user, we are inserting two rows in mailbox table with different senderid, receiverid, owner and folder(inbox/sent).

Upvotes: 1

Views: 150

Answers (1)

Alex Blex
Alex Blex

Reputation: 37048

To select largest number of messages exchanged between a user and any other user you can group by peer, order by count, and pick the first result:

SELECT count(*), if(owner=senderid, recipientid, senderid) as peer FROM mailbox
WHERE owner = 1472
GROUP BY 2
ORDER BY 1 desc
LIMIT 1

Upvotes: 1

Related Questions