I Have this table:
I need to find the number of uniqe conversation – conversation is define as senderid sent msg to reciverid, and reciverid has replied (no matter how many times or the thread length, it will be count as 1 conversation). so if senderid = 1, reeiver id =2 and in the next row senderid = 2 and reciever id =1 this is one conversation (till the end of time) Im really stock and not sure how to proceed.
You can use the functions
GREATEST() to create unique combinations of the 2 ids and aggregate:
SELECT COUNT(DISTINCT LEAST(m1.senderid, m1.receiverid), GREATEST(m1.senderid, m1.receiverid)) counter FROM Messages m1 WHERE EXISTS (SELECT 1 FROM Messages m2 WHERE (m2.receiverid, m2.senderid) = (m1.senderid, m1.receiverid))
See the demo.
Results (for your sample data):