CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
newUserNameHere
newUserNameHere

Reputation: 17981

Find most recent distinct records and sort by created_at in Postgresql

I thought this would be pretty easy to find a solution to, but I can't find a question that matches what I'm trying to do.

Here's what I would like to see (this query does not work):

SELECT DISTINCT ON (conversation_id) 
  *
FROM messages
ORDER BY created_at DESC

In other words, return the full rows with the most recent created_at dates where conversation_id is distinct, and then order by created_at DESC.

I wrote a query below that does exactly what I'm looking for but I think I overcomplicated it. I also assumed that the most recent record would have the highest ID and ordered by that in the sub query instead of "created_at" (it returns the same order as created_at DESC but is less reader friendly). 

SELECT 
  m.*
FROM (
  SELECT 
    DISTINCT ON (conversation_id) conversation_id,
    id
  FROM messages
  ORDER BY conversation_id, id DESC
) as t
INNER JOIN messages as m
  ON t.id = m.id
ORDER BY t.id DESC

Any help at making a simpler version of this query would be great.

Example added for clarification

If the messages table looks like this:

id, conversation_id, created_at, subject
1, 2,  "2014-10-21 00:01:00", "subject 1"
2, 43, "2014-10-21 00:02:00", "subject 2"
3, 12, "2014-10-21 00:03:00", "subject 3"
4, 2,  "2014-10-21 00:04:00", "subject 4"
5, 43, "2014-10-21 00:05:00", "subject 5"

Query should return the following:

id, conversation_id, created_at, subject
5, 43, "2014-10-21 00:05:00", "subject 5"
4, 2,  "2014-10-21 00:04:00", "subject 4"
3, 12, "2014-10-21 00:03:00", "subject 3"

Upvotes: 3

Views: 6096

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

There is no need for the join:

select *
from (
    select distinct on (conversation_id) *
    from messages
    order by conversation_id, id desc
) t
order by id desc

Upvotes: 9

vyegorov
vyegorov

Reputation: 22885

Per documentation:

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

So if you use DISTINCT ON (conversation_id), you should start ORDER BY clause with the same column.

Upvotes: 0

Related Questions