ORDER BY DESC with LIMIT doesn't return the newest rows, unless I ORDER BY twice

Question

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.

Could you add the table structure, sample data and expected results? Preferably as create table and insert statements in a fiddle — Jim Jones, Dec 22 '22 at 08:59
A [mcve] would make things clearer here. Make it easy to assist you! — jarlh, Dec 22 '22 at 09:05

Erwin Brandstetter · Accepted Answer · 2022-12-22T10:11:49.313

This would achieve your objective:

SELECT *
FROM  (
   SELECT DISTINCT ON (sender, receiver) *
   FROM   chats
   WHERE  login IN (sender, receiver)
   ORDER  BY sender, receiver, date DESC  -- plus tiebreaker column?
   ) sub
ORDER  BY date DESC, sender, receiver
LIMIT  30

See:

PostgreSQL DISTINCT ON with different ORDER BY

This picks the latest row per (sender, receiver) in the subquery sub. The sort order has to agree with DISTINCT ON, so the result is sorted by sender, receiver first and the latest rows overall are not necessarily on top.
Hence we need a 2nd ORDER BY in the outer SELECT. To get deterministic results in case of multiple pairs having the same latest date, append sender, receiver as tiebreaker (which are guaranteed to be unique at this stage of the query).

Remaining possible problems:

If login can be NULL, 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 inner ORDER BY as deterministic tiebreaker. Like the PK.
If date can be NULL, the descending sort order breaks. Fix with NULLS LAST in both instances. See:
- Sort by column ASC, but NULL values first?

Depending on undisclosed data distribution, there may be (much) faster query styles. See:

Select first row in each GROUP BY group?

I tried this query but the results differ from mine. I think the problem is that the distinct on (sender, receiver) will return a random row each time from the set of rows that have those sender+receivers. e.g. it will pick 1 message between alice+bob from any date and won't pick the newest one, since the sorting only happens after the DISTINCT step. — Kernel James, Dec 22 '22 at 09:35
@KernelJames No, `DISTINCT ON` does not return a random row. At worst, it can return an *arbitrary* row if not coupled with a deterministic sort order. I addressed remaining possible corner cases above. Read the detailed explanation in the [linked answer](https://stackoverflow.com/a/7630564/939860). — Erwin Brandstetter, Dec 22 '22 at 09:56

score 1 · Answer 2 · answered Dec 22 '22 at 08:59

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.

ORDER BY DESC with LIMIT doesn't return the newest rows, unless I ORDER BY twice

2 Answers2