mySQL query to select on the max value of one field with another field common

Question

1I am trying to write a message table with conversation threads like gmail. (I use php). The table is as shown. posID means the position of each message in a given threadID.

id | senderID | recvrID |  message(threadID,posID)  | threadID | posID |   time 
1       1          3            msg 1,1                  1          1       12pm
2       3          1            msg 1,2                  1          2        3pm
3       1          2            msg 2,1                  2          1        1pm

I need to write a query to find all msg threads to senderID=1 (in this case), and if there are more than one msgs in a thread, select the last msg only in each thread (can sort by positionID or time).

expected output is a table as follows.

 senderID      |  message                 | Time
   1               msg 1,2                  3pm
   1               msg 2,1                   1pm

Edit: After more reading, I guess I need to select messages with the sender/ receiver constraints, AND ( if more than one messages in a thread, only those messages with MAX(posID) for each threadID). Dont know how to implement this.

output is a table as follows. ______________________________________________________ senderID | message | Time ______________________________________________________ 1 msg 1,2 12pm 1 msg 2,1 1pm — aVC, May 12 '12 at 08:56
@Romil I have updated the question with the expected output. — aVC, May 12 '12 at 15:43
If you sort by `posID`, latest message is the on at `3pm`, while in your sample it is the one from `12pm`. So what is the right ordering? — vyegorov, May 12 '12 at 18:14
I think these questions will be of interest to you: http://stackoverflow.com/questions/tagged/greatest-n-per-group — Daan, May 12 '12 at 18:27

score 1 · Answer 1 · edited May 12 '12 at 18:42

1

Is this what you are looking for?

$user = '1';
$q="SELECT * FROM tablename WHERE senderID = '$user'";
$r=mysql_query($q);
while($w=mysql_fetch_array($r)){    
   $thread = $w['threadID'];
   $id = $w['id'];
   $message = $w['message'];
   $q2="SELECT * FROM tablename WHERE threadID = '$thread'".
        "ORDER BY posID DESC LIMIT 1";   
   $r2=mysql_query($q2);
   while($w2=mysql_fetch_array($r2)){
      $checkid = $w2['id'];
      if($checkid != $id){
         $message = $w2['message'];
      }
   }
   echo $message;
}

edited May 12 '12 at 18:42

vyegorov

21,787
7
59
73

answered May 12 '12 at 08:34

Nickolas Tuttle

208
2
10

I tried it, but it brings only one message, even when there are more than one threadIDs. – aVC May 12 '12 at 15:42

score 1 · Accepted Answer · answered May 12 '12 at 18:41

1

I think your question is misleading:

you actually need all threads where userID=1 is either sender or receiver;
for threadID=1 and posID=2 corresponding time is 3pm, but not 12pm as you've specified.

The following query will give you the last message in each thread by it's position:

SELECT senderID, recvrID, message, time
  FROM message m
  JOIN (SELECT threadID,max(posID) maxPOS FROM message
        GROUP BY threadID) mm
    ON m.threadID = mm.threadID AND m.posID = mm.maxPOS;

You can add the following filters:

WHERE senderID=1 for messages where userID=1 is only a sender. This will not match your sample though;
WHERE senderID=1 OR recvrID=1 for all messages, where userID=1 is participating. This will produce the expected results.

Try out the query here.

Note, that your table will not be able to handle cases where one message has multiple recipients.

answered May 12 '12 at 18:41

vyegorov

21,787
7
59
73

To @vyegorov, your solution worked. Sorry my question had some misleading data in te expected output section. you are right about the time for msg 1,2 (3 pm) and i have updated it. Also, this one [link](http://stackoverflow.com/a/4713912/903978) with some modification gets the same results. And in my application, there are no multiple recepients, so that should be all right. Thanks – – aVC May 12 '12 at 19:02
To @vyegorov, Can you help me with this? I am using this query SELECT DISTINCT * FROM mail INNER JOIN (SELECT threadID,MAX(time) as time FROM mail WHERE recipientID = "$id" GROUP by threadID ) Latest ON mail.threadID=Latest.threadID AND mail.time=Latest.time But this brings out the results with the newest dated messages at the bottom. Do you know how to sort the selection by time (NEWEST first). – aVC May 16 '12 at 02:45
@aVC, contact me over Skype, same nickname. – vyegorov May 16 '12 at 05:52
i sent a msg on skype,says it delivery pending. – aVC May 16 '12 at 06:33
Can you offer some hint on this one? http://stackoverflow.com/questions/11928108/mysql-query-within-a-query-with-privacy-condition-check – aVC Aug 13 '12 at 04:12

mySQL query to select on the max value of one field with another field common

2 Answers2