I am writing a chat app and want to get a list of recent contacts to show in the sidebar.
My table has the standard chat fields (sender, receiver, msg, date)
.
I’m currently getting a list of recent contacts by getting a list of messages with distinct (sender, receiver)
pair:
select * from (
select distinct on (sender, receiver) *
from (
select *
from chats
where sender = login or receiver = login
order by date desc
) as Q1
limit 30
) as Q2
order by date desc
The result returned is wrong as it doesn’t contain the newest messages.
I found that I could fix it by flipping the first order by
to order by date asc
. I only need to flip the first asc
and not the second.
Does anyone know why it works?
My only guess is that DISTINCT ON
is filtering from the bottom of the list rather than from the top.
2
Answers
There is no unique order of records when you ORDER BY rank. Even LIMIT 0,25 can return different rows on different executions if the table is updated or compacted (or after restoring the data from backup) and it is not wrong.
Use ORDER BY rank DESC, ID DESC, f.e., to be sure that the rows that rank equal are always returned in the same order.
I added ID DESC to get the most recent rows first. If the table contains a column that stores the last modified date then use it instead of ID. Or create another sorting rule that fits your application better.
This would achieve your objective:
See:
This picks the latest row per
(sender, receiver)
in the subquerysub
. The sort order has to agree withDISTINCT ON
, so the result is sorted bysender, receiver
first and the latest rows overall are not necessarily on top.Hence we need a 2nd
ORDER BY
in the outerSELECT
. To get deterministic results in case of multiple pairs having the same latestdate
, appendsender, receiver
as tiebreaker (which are guaranteed to be unique at this stage of the query).Remaining possible problems:
If
login
can beNULL
, the query breaks.If there can be multiple rows with the same latest
date
per(sender, receiver)
, an arbitrary row from this set is selected. Add another column to the innerORDER BY
as deterministic tiebreaker. Like the PK.If
date
can beNULL
, the descending sort order breaks. Fix withNULLS LAST
in both instances. See:Depending on undisclosed data distribution, there may be (much) faster query styles. See: