diego:

>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
>FROM accounts
>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
messages.

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
FROM accounts
INNER JOIN messages ON messages.to_id = accounts.id
WHERE accounts.id = 1 AND messages.deleted = 0
ORDER BY messages.to_id DESC
LIMIT 1

Beware, this will return no rows if the account doesn't have any
received messages.


--
Erick