title: SQL query involving inner join and group by
body: <p>I need help with a MySQL query that will optimize my Rails 3 application.</p>
  
  <p>I have 2 tables: segments and custom properties.</p>
  
  <p>Table defs are:</p>
  
  <p>segments<br/>
  - name<br/>
  - organization_id<br/>
  - ancestry<br/>
  - ancestry_depth<br/></p>
  
  <p>custom_properties<br/>
  - name<br/>
  - label<br/>
  - value<br/>
  - segment_id<br/></p>
  
  <p>Segments are tree-like structures, thus the ancestry columns.  e.g. Segment 3 is a child of Segment 2 is a child of Segment 1.  Each of those segments will have the same custom properties, however, the values could come from an inherited segment (a segment up the tree) instead of from the segment itself.  If a value of a particular property is inherited from a segment up the tree, then its value is set to null.</p>
  
  <p>My goal is to retrieve custom properties for a particular segment, in a single SQL statement, while taking into consideration the depth of the segment since depth determines the priority order of the values that I need.</p>
  
  <p>The current query I have is very close, it returns the correct number of properties for a particular segment, however, with that group by statement I always get the property with the lowest ID, but what I really need is the property with the highest depth.  If I take out the group by then I get duplicate properties, 1 from each segment listed in the "IN".  But again, I only need property from the highest depth.</p>
  
  <pre><code>SELECT custom_properties.name, 
         custom_properties.value, 
         segments.ancestry_depth 
  FROM   custom_properties 
         INNER JOIN segments 
           ON custom_properties.object_id = segments.id 
  WHERE  custom_properties.object_type = 'Segment' 
         AND custom_properties.object_id IN ( 86770, 86637, 40667, 180 ) 
         AND custom_properties.value IS NOT NULL 
  GROUP  BY custom_properties.name 
  ORDER  BY custom_properties.name, 
            segments.ancestry_depth DESC 
  </code></pre>
  
  <p>I hope this makes sense.  I appreciate any help and/or direction.</p>
  
  <p>Thank you!</p>
  
  <p>Louis</p>