CODEWITHSUNDEEP
sqlpostgresql
Prosto Trader
Prosto Trader

Reputation: 3527

Select distinct last messages by pairs in postgres

I have a table with messages:

enter image description here

I need to only one LAST by date and time conversation between two users and here is what i do:

select  distinct on (user_from, user_to)
*
from messages

Here is what I get:

enter image description here

I do get one unique conversations lines between two users, but its not the last one.

How should I select only the last one unique conversation between two users?

EDITED: expected output:

enter image description here

Upvotes: 0

Views: 143

Answers (2)

Houari
Houari

Reputation: 5641

You can achieve this using Window Functions

SELECT * 
FROM
  (select *, rank() OVER (PARTITION BY date, user_from,user_to ORDER BY time desc ) AS pos
     FROM messages
  ) AS msg

  WHERE pos = 1

I strongly advice you to note use PostgreSQL's tokens as column name!

Upvotes: 0

Prosto Trader
Prosto Trader

Reputation: 3527

Here is how I did it:

SELECT DISTINCT ON (user_from, user_to)   *
FROM messages
ORDER BY user_from, user_to, date+time DESC

Upvotes: 1

Related Questions