How to get a list of conversations from the message table？

Question

Rows as follow:

|id.   |sender|receiver| type| content   | time |
|------|------|--------|-----|-----------|------|
|uuid01|     x|       y| text|      hello|time01|
|uuid02|     y|       z| text|how are you|time02|
|uuid03|     y|       x| text|       haha|time03|
|uuid04|     x|       y|image|           |time04|

How can i merge x->y / y->x to be one conversation? As follows:

|id |type | content   | time |
|---|-----|-----------|------|
|x-y|image|           |time04|
|y-z|text |how are you|time02|

Answer 1

Assuming all involved columns NOT NULL.

SELECT DISINCT ON (LEAST(sender, receiver), GREATEST(sender, receiver))
       LEAST(sender, receiver) || '-' || GREATEST(sender, receiver) AS id
     , type, content, time
FROM   tbl
ORDER  BY LEAST(sender, receiver), GREATEST(sender, receiver), time DESC;

See:

• Select first row in each GROUP BY group?
• SQL Sorting table based on two interchangeable fields
• How to create a Postgres table with unique combined primary key?

Performance optimization is possible, depending on undisclosed details of your relational design and data distribution.