Postgres query for DISTINCT for either of 2 columns

Question

I have a messages table. As a side note all ids are UUIDs not INTs

messages
 - id
 - from_id
 - to_id
 - text
 - inserted_at

I want to retrieve the last message that was either sent or received by user x with every other user.

So given a table with this data

--------------------------------------------------
| id |  from_id  | to_id  |  text   | inserted_at |
--------------------------------------------------- 
| 1 |      1     |    2   |  hello  | 2020-01-01  |
| 2 |      2     |    1   |  sup    | 2020-01-02  |
| 3 |      1     |    3   |  hello  | 2020-01-01  |
| 4 |      2     |    3   |  howdy  | 2020-01-01  |

I want to retrieve the messages for user 1.

Pseudo SQL

SELECT DISTINCT OR (from_id, to_id) text, inserted_at 
FROM messages 
WHERE from_id = 1 OR to_id = 1 
ORDER BY inserted_at;

The result should be:

--------------------------------------------------
| id |  from_id  | to_id  |  text   | inserted_at |
--------------------------------------------------- 
| 2 |      2     |    1   |  sup    | 2020-01-02  |
| 3 |      1     |    3   |  hello  | 2020-01-01  |

Thorsten Kettner · Accepted Answer · 2021-01-12 00:49:05Z

2

Use LEAST and GREATEST to get the to/from tuples ordered:

SELECT DISTINCT ON (LEAST(from_id, to_id), GREATEST(from_id, to_id))
  from_id, to_id, text, inserted_at
FROM messages 
WHERE from_id = 1 OR to_id = 1 
ORDER BY LEAST(from_id, to_id), GREATEST(from_id, to_id), inserted_at;

answered Jan 12, 2021 at 0:49

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick · Accepted Answer · 2021-01-12 00:45:30Z

2

You can order messages for each user by partitioning your data by LEAST(from_id, to_id), GREATEST(from_id, to_id) and ordering by inserted_at, then you can select only the first rows from that set:

WITH CTE AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY LEAST(from_id, to_id), GREATEST(from_id, to_id) ORDER BY inserted_at DESC) AS rn
  FROM messages
)
SELECT id, from_id, to_id, text, inserted_at
FROM CTE
WHERE (from_id = 1 OR to_id = 1)
  AND rn = 1
ORDER BY id

Output:

id  from_id     to_id   text    inserted_at
2   2           1       sup     2020-01-02T00:00:00.000Z
3   1           3       hello   2020-01-01T00:00:00.000Z

Demo on db-fiddle

answered Jan 12, 2021 at 0:45

Nick

147k23 gold badges67 silver badges106 bronze badges

2 Comments

ThreeAccents Over a year ago

what if I'm using UUID for ids?

Nick Over a year ago

That should still work as both inputs just need to be the same datatype, and comparable.

Collectives™ on Stack Overflow

Postgres query for DISTINCT for either of 2 columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related