PostgreSQL: Selecting distinct values on single column while sorting on another

Question

I'm working with a simple db of messages.

 id           | integer                     
 parent_id    | integer                     
 msg          | character varying(140)       
 create_dte   | numeric(10,0)

Messages have a parent_id of -1 (top-level) or the id of another message. I want to return most recent top-level messages--most recent defined as recent create_dte of the parent or any of its children.

Here is my query:

select (case when parent_id != -1 then parent_id else id end) as mid, create_dte
from messages
order by create_dte desc

My result is something like this:

 mid  | create_dte 
------+----------------------
 5655 |           1333906651
 5462 |           1333816235
 5496 |           1333686356
 5157 |           1333685638
  676 |           1333648764
 5493 |           1333648630
  724 |           1333641318
 5402 |           1333470056
 5397 |           1333468897
 5396 |           1333468378
 3640 |           1333304212
 3434 |           1333300366
 2890 |           1333293369
 4958 |           1333288239
 4899 |           1333287641
 5203 |           1333287298
 4899 |           1333287275
 4899 |           1333285593

How do I eliminate the duplicates in the result while maintaining the sort of create_dte? I've tried distinct and distinct on but always lose the sorting.

Here is what I need:

mid  | create_dte 
------+----------------------
 5655 |           1333906651
 5462 |           1333816235
 5496 |           1333686356
 5157 |           1333685638
  676 |           1333648764
 5493 |           1333648630
  724 |           1333641318
 5402 |           1333470056
 5397 |           1333468897
 5396 |           1333468378
 3640 |           1333304212
 3434 |           1333300366
 2890 |           1333293369
 4958 |           1333288239
 4899 |           1333287641
 5203 |           1333287298

(Last two rows not returned as 4899 already appears in result with a more recent create_dte.)

Thanks

Answer 1

Unless I misunderstood your question, you need to walk the whole hierarchy for any message, you will need a recursive query to get all messages for one "root" message id. Once you have that, it's a simple max() on the create_dte:

with msg_tree as (
    select id as root_id,
           id, 
           parent_id,
           create_dte
    from messages
    where parent_id = -1

    union all 

    select p.root_id,
           c.id,
           c.parent_id,
           c.create_dte
    from messages m 
        join msg_tree p on p.id = m.parent_id
)             
select root_id, 
       max(create_dte)
from msg_tree
group by root_id
order by 2;

Answer 2

try the following:

select (case when parent_id != -1 then parent_id else id end) as mid, max(create_dte )
from messages 
group by case when parent_id != -1 then parent_id else id end
order by max(create_dte) desc;