SELECT DISTINCT not working after CASE statement
I'm trying to return the first message between two users, whether I sent that message or received it. I've coded the below query which returns all the correct threads, but seems to ignore the DISTINCT clause - and I end up with multiple results with the same 'someid'.
How do I correct this query so that there will be no duplicate values returned in the resultant 'someid' column?
Here's what I'm currently using:
SELECT DISTINCT CASE WHEN $userid != senderid THEN senderid ELSE GROUP_CONCAT(receivers.id SEPARATOR ', ') END someid,
CASE WHEN $userid != senderid THEN sen开发者_运维技巧ders.username ELSE GROUP_CONCAT(receivers.username SEPARATOR ', ') END somename,
messages.body,
messages.time
FROM messages
LEFT JOIN messages_recipients AS recipients ON messages.id = recipients.messageid
LEFT JOIN users AS senders ON messages.senderid = senders.id
LEFT JOIN users AS receivers ON recipients.userid = receivers.id
WHERE recipients.userid = $userid
OR messages.senderid = $userid
GROUP BY messages.id
ORDER BY messages.time DESC
As @Mitch Wheat mentioned, you probably have to separate the DISTINCT into an outer query.
SELECT DISTINCT * FROM
(
SELECT CASE WHEN ... GROUP BY messages.id ORDER BY messages.time DESC
) AS inner_q
ORDER BY messages.time DESC
But remember, DISTINCT is for distinct records not a single value (someid). messages.time may preventing you from getting what you are after.
精彩评论