Re: get last message from each user - sql question
>im building a little messaging system and i want to get a list
>of users and the last message sent to the conversation
But below, you'r not going for "a list of users". It's merely for one
single user, right?
>i tried with this but im getting the first message!
No, you're getting a some message, not necessarily the first one. After
all, you'r not specifying any order.
>suppose we want to know the conversations for the account #1
>SELECT accounts.id, accounts.name, messages.id, messages.content
>INNER JOIN messages ON (messages.from_id = accounts.id AND
>messages.to_id = 1) OR (messages.to_id = accounts.id AND
>messages.from_id = 1) AND messages.deleted = 0 WHERE accounts.deleted
>= 0 GROUP BY accounts.id
Looks like you possibly want two messages from this account: a received
message and a sent message. It is also not clear what you want to have
returned, if that account doesn't have any received or any sent
If we assume "last message" to be the one with the highest ID, then his
latest (probably not last!) received message would be
SELECT accounts.id, accounts.name, messages.id, messages.content
INNER JOIN messages ON messages.to_id = accounts.id
WHERE accounts.id = 1 AND messages.deleted = 0
ORDER BY messages.to_id DESC
Beware, this will return no rows if the account doesn't have any