CODEWITHSUNDEEP
mysqlsql
superzamp
superzamp

Reputation: 511

SQL query to retrieve conversations from messages?

I'm trying to get conversations from my private messages table with a single elegant SQL query. Here's a simplified version of my table:

Sample table

Let's say I want to retrieve all James's conversations. The lines highlighted in green are the ones I want him to have in his inbox. In other words, I want to select the latest messages james has received, grouped by sender. I've tried the following query :

SELECT *
FROM "messages"
WHERE To = "James"
ORDER BY Id DESC
GROUP BY `From`

But MySQL returns me these rows :

Sample table

So how could I fix this request ?

Thanks !

EDIT: I Shouldn't have used "From" as a field name, I won't change it now to avoid breaking the answers but sorry about that.

Upvotes: 2

Views: 3558

Answers (5)

Duilio
Duilio

Reputation: 258

This question is quite old, but in case someone else has a similar doubt, this was my solution:

Assuming James has the ID 1 (I use ID but feel free to replace 1 with "James" a username, an ID, etc.)

SELECT IF(`from` = 1, `to`, `from`) AS `friend_id`, `message`, `created_at` 
FROM `messages` 
WHERE `from` = 1 OR  `to` = 1
GROUP BY friend_id
ORDER BY `created_at`  DESC

I'm not sure if this is the best possible solution, but it fetches the right messages. The trick was to use an IF to get the friend_id (it can be in the "from" or in the "to" column

Upvotes: 0

Tin Tran
Tin Tran

Reputation: 6202

SELECT M.Id,
   M.`From`,
   M.`To`,
   M.message
FROM messages M
INNER JOIN
    (SELECT `from`, max(Id) as maxId
     FROM messages
     WHERE `to` = "James"
     GROUP BY `from`)T
ON M.Id = T.maxId

You say you want messages that 'James' has received but you compare from = 'James' is that a mistake?

Edit: @Lotharyx wanted to know if there's a way to do it without a subquery, so here's one way. see second query in this sqlFiddle

SELECT IF(@prevFrom IS NULL OR @prevFrom != M.`From`,@row:=1,@row:=@row +1) as row,
       @prevFrom:=M.`From`,
       M.id, M.`From`, M.`To`, M.message
FROM messages M
WHERE `to` = 'James'
HAVING row = 1
ORDER BY M.`From`, M.Id DESC;

Upvotes: 4

MillaresRoo
MillaresRoo

Reputation: 3848

From is a reserved word, enclose it with backticks.

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Upvotes: 1

Brian A. Henning
Brian A. Henning

Reputation: 1511

You don't need a GROUP BY clause, as you're not using an aggregating function. Remove it and you should get the results you want. Someone else suggested a query featuring a self-join and subquery, which is needlessly complex.

SELECT * FROM `messages` WHERE `To` = 'James' ORDER BY `Id` DESC LIMIT 5

That'll give you the 5 most recent messages to James. Change the LIMIT argument as needed.

Note that LIMIT is a mysql-specific extension, but you tagged with mysql so I presume that is the engine you are using.

Upvotes: 0

Rahul
Rahul

Reputation: 77934

should be like this instead

SELECT * FROM messages WHERE `From` = 'James' ORDER BY Id DESC

Upvotes: 0

Related Questions