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

Question

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

Answer 1

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