jeudi 23 juin 2016

MySQL GROUP BY acting strange, not returning expected result

I'm trying to return the row with ID 258776, here's what I've tried so far...

Without GROUP BY:

SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at FROM (
                SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
                INNER JOIN message AS msg ON i.message_id = msg.id
                WHERE i.profile_id = 2135
                AND i.is_sent = 0
                AND i.is_deleted = 0
                AND msg.uid = '570cc3a568402'
                ORDER BY msg.updated_at DESC
            ) AS main #GROUP BY main.uid


= ID =  =MSG =  =INB =  = UID       =   = BD =   = CREATED         =  
258776  258776  524785  570cc3a568402   wtf      2016-06-22 11:34:29  
217149  217149  438907  570cc3a568402   <br />   2016-04-12 11:45:09  

Try with GROUP BY

SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at FROM (
                SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
                INNER JOIN message AS msg ON i.message_id = msg.id
                WHERE i.profile_id = 2135
                AND i.is_sent = 0
                AND i.is_deleted = 0
                AND msg.uid = '570cc3a568402'
                ORDER BY msg.updated_at DESC
            ) AS main GROUP BY main.uid

= ID =  =MSG =  =INB =  = UID       =   = BD =   = CREATED         =  
217149  217149  438907  570cc3a568402   <br />   2016-04-12 11:45:09  

Switch to ASC but GROUP BY gives same results ?

SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at FROM (
                SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
                INNER JOIN message AS msg ON i.message_id = msg.id
                WHERE i.profile_id = 2135
                AND i.is_sent = 0
                AND i.is_deleted = 0
                AND msg.uid = '570cc3a568402'
                ORDER BY msg.updated_at ASC
            ) AS main GROUP BY main.uid

= ID =  =MSG =  =INB =  = UID       =   = BD =   = CREATED         =  
217149  217149  438907  570cc3a568402   <br />   2016-04-12 11:45:09

I presume it should work fine if I didn't need to use an INNER join ? :(

Aucun commentaire:

Enregistrer un commentaire