How to limit query results with Mysql

Question

Newbie question:

I've got an email message table that's got a message_id field (primary key field) and other fields like body, receiver_id, sender_id and parent_id. The parent_id field is what connects messages of the same thread together when user replies to a message.

Right now i have a simple query that looks up all the messages under a specific reciever_id and returns them. The problem is I want to also check to see if 2 or more messages have got the same parent_id and if so, i only want the most recent messsage returned.

Can anyone shed some light on this?

I thought about using a CASE statment but wasn't sure where to start.

Answer 1

I would add a timestamp to the field for when it was submitted. You could them use something similar to the following:

SELECT body, receiver_id, sender_id, parent_id
FROM messages 
GROUP BY parent_id
ORDER BY timestamp DESC
LIMIT 1

Note: No way to fully know if that query works without trying it on your table, but hopefully it points you in the right direction.

Edit: @liysd made a good point, the order by will merge. You will need to do a subquery against iself.

SELECT *
FROM (SELECT * FROM messages WHERE parent_id = id)
ORDER BY timestamp DESC
LIMIT 1

Answer 2

You may use nested query as below:

    select * 
    from messages A 
    where A.receiver_id=:receiver_id
    (and timestamp = (select max(timestamp) 
                     from message B 
                     where A.parent_id = B.parent_id and A.parent_id<>NULL ))
    or A.parent_id = NULL