Wrestling with SQL

Question

I have this in a MySQL db:

table Message
    sender_id int
    recipient_id int
    created datetime
    [title, body, etc... omitted]

Is it possible to get from this, in a single query, a list of all the users who have been communicating with a given user with id N (meaning N appears in either sender_id or recipient_id), ordered by created, including the most recent value of created, without duplicates (no sender/recipient pairs N, M and M, N)?

I'm pretty sure the answer is No, but I'm having trouble convincing myself. Afternoon mental dullness.

If No, what's the most efficient alternative you would recommend?

---

EDIT: holy crap, Stack Overflow is hopping these days. 8 answers before I realize I forgot to specify that I would like to have the most-recent value of created present along with the other user ID in each row.

Answer 1

Try this

SELECT distinct recipient_id
FROM Message
WHERE sender_id = @id

UNION

SELECT distinct sender_id
FROM Message
WHERE recipient_id = @id

The union clause will remove duplicates across the two queries. Actually looking at it you won't need the distinct's either as the union will do that for you too (anyone any idea if it's more efficient to use distinct to pre-filter the clauses or just let the union handle all the duplication?)