CODEWITHSUNDEEP
mysqlsql
Matthew Walker
Matthew Walker

Reputation: 193

SQL retrieve most recent record of each threadID

Table Structure:

id | sender | receiver | subject | message | threadID | timestamp | date | readStatus

SELECT * FROM mail 
WHERE sender = 'redphyre' OR receiver = 'redphyre' 
AND threadID BETWEEN (select min(threadID) FROM mail WHERE sender = 'redphyre' OR receiver = 'redphyre' ORDER BY id DESC) 
AND (select max(threadID) FROM mail WHERE sender = 'redphyre' OR receiver = 'redphyre' ORDER BY id DESC) 
GROUP BY threadID;

I'm trying to get this to return the most recent record for user redphyre for each individual threadID in this case threadID's 0,1,2. But it isn't working like that. Instead I am getting mixed results between the threadIDs. Anyone have any thoughts?

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Presumably, this is what you want:

SELECT m.*
FROM mail m
WHERE 'redphyre' IN (m.sender, m.receiver) AND
      m.id = (SELECT MAX(m2.id)
              FROM mail m2
              WHERE 'redphyre' IN (m2.sender, m2.receiver) AND m2.threadID = m.threadID
             );

BETWEEN and GROUP BY really have nothing to do with what you want. The subquery considers all matching records on the same thread and returns the one with the largest id.

Upvotes: 1

Related Questions