mysqlsqlgroup-bydistinctinstant-messaging

MySQL Distinct / Group by, breaking my head with this one


So, what I'm trying to do is to retrieve a list of all "initial" messages a person sees in their messaging window

This is the table structure

thread_id | sender | receiver | message | date | sender_deleted | sender_received | read
xRdaQ     | bTP5n  | lCBNA    | hello!  | date | 0              | 0               | 
xRdaQ     | lCBNA  | bTP5n    | hey!    | date | 0              | 0               | 
1T4xR     | bTP5n  | An03R    | hhi     | date | 0              | 0               | 

The queries I tried so far:

select * from messages where sender = 'bTP5n'
union select * from messages where receiver = 'bTP5n'
group by conversation_id

And I still get the two rows with the same thread_id

The same with this one query:

select * from messages where sender = 'bTP5n'
union select * from messages where receiver = 'bTP5n'
group by conversation_id order by date desc

Both of them are failing to return what I want, which is all unique thread_id where the sender or the receiver is equal to "bTP5n"

Disclaimer: Dummy data was used for this question


Solution

  • If you are using group by in second union query then it is only apply to the second query , if you want to apply in all the result then you have to write group by outside of all the results. Try below query:

    select * from 
    (select * from messages where sender = 'bTP5n' 
     union 
     select * from messages where receiver = 'bTP5n' 
    ) 
    as a group by conversation_id order by date desc