0

I'm building a messaging feature for an app, my database table looks like this:

messages:
  id
  senderId
  receiverId
  threadId   <-- every message is part of a thread
  createdAt

I have a 'Messages' page which has a card for every message thread, but I want it to show the latest message in the card as a preview for each thread. This is the query I've written so far:

SELECT DISTINCT ON ("threadId") * FROM messages ORDER BY "createdAt" DESC;

So I'm doing DISTINCT ON ("threadId"), but I'm also selecting all the columns because I want all the data, that's why there's a * after the DISTINCT ON clause. This query worked fine up until this point, but now it's failing on the ORDER BY clause. I want to return the message that was created last, but I'm getting an error:

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT DISTINCT ON ("threadId") * FROM messages order by "cr...

I understand the error I think (the ORDER BY column has to match the column that DISTINCT ON is being applied to) but I don't know how to get the query to be what I want, which is to be ordered by createdAt before it becomes distinct.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Abdul Ahmad
  • 9,673
  • 16
  • 64
  • 127

1 Answers1

2

Leading expressions in ORDER BY have to agree with expressions in DISTINCT ON:

SELECT DISTINCT ON ("threadId") *
FROM   messages
ORDER  BY "threadId", "createdAt" DESC;

Detailed explanation:

If you want to order results in a different way (like commented), you'll have to wrap the query in an outer query with a second ORDER BY. See:

Or similar, depending on your exact situation and requirements. There may be sophistication to get best results. Recent example:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228