开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜