CODEWITHSUNDEEP
phpmysqlselect
David D
David D

Reputation: 1295

How to retrieve grouped messages ordered by date SQL

Let's pretend I have the following "messages" table

ID (auto)   Sender     Receiver    Msg        Date (datetime)
1           Dave       John        Hi         01/01/2013
2           John       Dave        Hello      02/01/2013
3           James      Dave        U there?   02/02/2013
4           Dave       James       Yup        02/03/2013
5           Dave       Simon       Hey        02/03/2013

I want the list of people Dave is talking to ordered by Date. 

Simon
James
John

I'm getting stuck on how to Group them without including "Dave" in one query.

Many thanks.

Upvotes: 1

Views: 263

Answers (3)

ajtrichards
ajtrichards

Reputation: 30565

This will give you everyone Dave has sent a message to and recieved a message from ordered by Date.

SELECT DISTINCT(Receiver) FROM messages WHERE Sender = 'Dave' ORDER BY date DESC

Upvotes: -1

DevlshOne
DevlshOne

Reputation: 8457

SELECT Receiver FROM messages WHERE Sender = "Dave"
AND Receiver NOT IN (SELECT Sender FROM messages WHERE Receiver = "Dave")
UNION
SELECT Sender FROM messages WHERE Receiver = "Dave"

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You seem to want the distinct list of people that Dave is talking to:

select (case when receiver = 'Dave' then Sender else Receiver end)
from messages m
where 'Dave' in (Receiver, Sender)
group by (case when receiver = 'Dave' then Sender else Receiver end)
order max(date) desc;

Upvotes: 3

Related Questions