CODEWITHSUNDEEP
mysql
aVC
aVC

Reputation: 2344

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

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.

Upvotes: 1

Views: 1125

Answers (2)

Nickolas Tuttle
Nickolas Tuttle

Reputation: 208

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;
}

Upvotes: 1

vyegorov
vyegorov

Reputation: 22855

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.

Upvotes: 1

Related Questions