Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
Posting your current data and your expected result in a tabular form gives better results than posting the "wrong query" results. – Mosty Mostacho Apr 10 '12 at 6:34
Thanks. Added this. – Ty Kroll Apr 10 '12 at 6:51

2 Answers

up vote 2 down vote accepted

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;
share|improve this answer
ERROR: column "messages.create_dte" must appear in the GROUP BY clause or be used in an aggregate function – Ty Kroll Apr 10 '12 at 6:43
i have edited my answer, please try. – Vikram Apr 10 '12 at 6:52
Alright, Vikram. Thanks a bunch. – Ty Kroll Apr 10 '12 at 7:38
1  
Cleaned it up a tiny bit: select (case when parent_id != -1 then parent_id else id end) as mid, max(create_dte) as mmax from messages group by mid order by mmax desc; – Ty Kroll Apr 10 '12 at 7:41
@bubba_gump: but that will only look at create_dt of direct children, not of grand-children. How many levels does your message hierarchy have? – a_horse_with_no_name Apr 10 '12 at 7:52
show 1 more comment
up vote 1 down vote

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;
share|improve this answer
Yes, my original query does this. I also need to keep these sorted by create_dte desc. – Ty Kroll Apr 10 '12 at 7:36
@bubba_gump: Then just add an order by. – a_horse_with_no_name Apr 10 '12 at 7:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.