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

Posting your current data and your expected result in a tabular form gives better results than posting the "wrong query" results. — Mosty Mostacho
– Mosty Mostacho, Commented Apr 10, 2012 at 6:34

Vikram · Accepted Answer · 2012-04-10 06:52:11Z

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;

edited Apr 10, 2012 at 6:52

answered Apr 10, 2012 at 6:00

Vikram

8,32335 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ty Kroll Over a year ago

ERROR: column "messages.create_dte" must appear in the GROUP BY clause or be used in an aggregate function

Ty Kroll Over a year ago

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;

user330315 Over a year ago

@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?

Ty Kroll Over a year ago

Only a single level. I should have mentioned that originally. I now understand your solution, and it definitely solves the query for n-levels.

score 1 · Accepted Answer · 2012-04-10 07:44:39Z

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;

edited Apr 10, 2012 at 7:44

answered Apr 10, 2012 at 7:14

user330315

2 Comments

Ty Kroll Over a year ago

Yes, my original query does this. I also need to keep these sorted by create_dte desc.

user330315 Over a year ago

@bubba_gump: Then just add an order by.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related