Get last row PER Group

Question

How can I formulate a query for the below task:

Let's say you are logged in as user:1 I want to get one row per conversations I've had. For each row I want to get, the "Subject" of the first row within the conversation "DateTime" of the first row "Message" last message of this conversation no matter who wrote it

CREATE TABLE messages (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    FromID INT NOT NULL,   
    ToID INT NOT NULL,    
    ConversationID INT NOT NULL,    
    Subject varchar(255), 
    Message varchar(255),
    DateTime DATETIME                                       
    ) ENGINE=InnoDB;


CREATE TABLE conversations (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY                                    
    ) ENGINE=InnoDB;



INSERT INTO conversations (ID) VALUES (1), (2), (3);
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 1, "Hi", "This is a test message", "2010-08-08 16:23:48");        
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 1, "", "Hey again you have not answered", "2010-08-08 16:23:52");                                                                                                                                               
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (2,1, 1, "", "Hi this is my answer", "2010-08-08 16:23:59");


INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (2,1, 2, "2.Hi", "2.This is a test message", "2010-08-08 16:25:48");        
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 2, "", "2.Hi back", "2010-08-08 16:25:52");                                                                                                                                               
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (2,1, 2, "", "2.Hi this is my answer", "2010-08-08 16:25:59");


INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (2,1, 3, "3.Hi", "3.This is a test message", "2010-08-08 16:27:48");        
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 3, "", "2.Hi back", "2010-08-08 16:27:52");   
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 3, "", "2.Hello are you there?", "2010-08-08 16:27:59");                                                                                                                                            

Answer 1

Take a look @ ROW_NUMBER() in MySQL - you'll be able to apply this to your problem for sure.

Answer 2

SELECT M.ConversationID, 
MAX(CASE WHEN M.DateTime = X.FirstRow THEN M.Subject END) AS Subject,
CAST(COALESCE(MAX(CASE WHEN M.DateTime = X.LastRowSentByOtherUser 
                       THEN M.DateTime END),X.LastRow) AS DateTime)AS LastTime,
MAX(CASE WHEN M.DateTime = X.LastRow THEN M.Message END) AS Message,
MAX(CASE WHEN FromID = 1 THEN ToID ELSE FromID END) AS OtherParticipantId
FROM messages M
JOIN (
    SELECT ConversationID, MIN(DateTime) AS FirstRow, MAX(DateTime) AS LastRow,
    MAX(CASE WHEN FromID<>1 THEN DateTime END) AS LastRowSentByOtherUser
    FROM messages
    WHERE FromID=1 OR ToID=1
    GROUP BY ConversationID
) X ON X.ConversationID = M.ConversationID
AND (M.DateTime IN (X.FirstRow, X.LastRow, X.LastRowSentByOtherUser))
GROUP BY M.ConversationID
HAVING MAX(CASE WHEN M.DateTime = X.LastRowSentByOtherUser 
                   THEN M.DateTime END) IS NOT NULL

Answer 3

You should try something like this:

SELECT 
   m1.Subject,
   m1.DateTime,
   m2.Message
FROM conversations c 
INNER JOIN 
   (SELECT MIN(ID) AS minID, 
           MAX(ID) AS maxID, 
           ConversationID
   FROM messages
   WHERE FromID = @userID OR ToID = @userID
   GROUP BY ConversationID) AS cGrouped
ON c.ConversationID = cGrouped.ConversationID
INNER JOIN messages m1 ON m1.ID = cGrouped.minID
INNER JOIN messages m2 ON m2.ID = cGrouped.maxID