How can I select the rest of the row with DISTINCT?
I have a table where I keep messages and one where I keep users. I want to
get all the users that interactioned (send or received a message) with
user_id 1. This query works:
http://sqlfiddle.com/#!2/6a2f3/1
EDIT:
SELECT DISTINCT
(CASE WHEN `user_to_id` = 1 THEN `user_from_id` ELSE `user_to_id` END)
`user_id`,
users.*
FROM `messages`
INNER JOIN users
ON (CASE WHEN `user_to_id` = 1 THEN `user_from_id` ELSE `user_to_id` END)
= users.user_id
WHERE `user_to_id` = 1 OR `user_from_id` = 1
ORDER BY `time` DESC
But if I add to SELECT the message column, it returns duplicate records:
http://sqlfiddle.com/#!2/6a2f3/2
EDIT:
SELECT DISTINCT
(CASE WHEN `user_to_id` = 1 THEN `user_from_id` ELSE `user_to_id` END)
`user_id`,
`messages`.`message`,
users.*
FROM `messages`
INNER JOIN users
ON (CASE WHEN `user_to_id` = 1 THEN `user_from_id` ELSE `user_to_id` END)
= users.user_id
WHERE `user_to_id` = 1 OR `user_from_id` = 1
ORDER BY `time` DESC
How can I fix that?
And also, I see that it orders the results after the "DISTINCT" selection
was made. The first query should return the results inverted because the
row with message_id 2 has time 3. Is there a way I can order them before
the "DISTINCT"?
EDIT 2: I wasn't clear about the question. I want to select only the last
message for a matched user_id.
No comments:
Post a Comment