CODEWITHSUNDEEP
sqlpostgresqldistinct
user3434212
user3434212

Reputation: 87

Sql query distinct + other columns

For example, I have such sql table of messages:

Id   from_user_id   to_user_id    message
1    1              2             Hello
2    1              2             How are you?
3    1              3             Where are you?

And query SELECT DISTINCT to_user_id FROM messages; it returns

to_user_id
2
3

But, that is not enough. I need to show all last messages of from_user_id(id=1) to others users, and avoid N+1 queries problem. The result must be like this

Id   from_user_id   to_user_id    message
2    1              2             How are you?
3    1              3             Where are you?

Upvotes: 1

Views: 161

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Postgres offers distinct on, which is generally the best way to approach this type of problem:

select distinct on (to_user_id) m.*
from messages m
where m.from_user_id = 1
order by to_user_id, id desc;

Upvotes: 1

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

You could use the analytic function rank, and order it within a group...

select * from (
select id,
       from_user_id,
       to_user_id,
       message,
       rank () over (partition by from_user_id, to_user_id order by id desc) rnk
from   table_name
) t1 where rnk = 1

This is assuming that the id column is sequentially generated number, so message with id 3 was created after message with id 2. Usually, if you have a timestamp column, it would be more logical. In that case, you could use order by timestamp desc

Upvotes: 1

Parris Varney
Parris Varney

Reputation: 11478

You're going to want to do a self join:

SELECT    m.*
FROM      messages m
LEFT JOIN messages _m ON m.to_user_id = _m.to_user_id
AND       _m.id > m.id
WHERE     _m.id IS NULL

Upvotes: 1

Related Questions