title: PostgreSQL: Selecting distinct values on single column while sorting on another
body: <p>I'm working with a simple db of messages.</p>
  
  <pre>
   id           | integer                     
   parent_id    | integer                     
   msg          | character varying(140)       
   create_dte   | numeric(10,0)
  </pre>
  
  <p>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.</p>
  
  <p>Here is my query:</p>
  
  <pre><code>select (case when parent_id != -1 then parent_id else id end) as mid, create_dte
  from messages
  order by create_dte desc
  </code></pre>
  
  <p>My result is something like this:</p>
  
  <pre>
   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
  </pre>
  
  <p>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.</p>
  
  <p>Here is what I need:</p>
  
  <pre>
  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
  </pre>
  
  <p>(Last two rows not returned as 4899 already appears in result with a more recent create_dte.)</p>
  
  <p>Thanks</p>