title: Mysql: Select unique values within the group?
body: <p>There are 3 columns - category, material and brand. To be honest it's not a columns from single table but the result of a query from 8 another tables using joins ;)</p>
  
  <pre><code>    |   category  |   material   | brand          |
      ----------------------------------------------|
      |  engines    |  product 1   | abb            | -&gt; unique for category "engines"
      |  engines    |  product 2   | wika           | -&gt; unique for category "engines"
      |  engines    |  product 3   | allen-bradley  | -&gt; unique for category "engines"
      |  engines    |  product 5   | wika           |
      |  engines    |  product 6   | e+h            | -&gt; unique for category "engines"
      |  drives     |  product 7   | abb            | -&gt; unique for category "drives"
      |  drives     |  product 8   | wika           | -&gt; unique for category "drives"
      |  drives     |  product 9   | allen-bradley  | -&gt; unique for category "drives"
      |  drives     |  product 10  | e+h            | -&gt; unique for category "drives"
      |  drives     |  product 11  | e+h            | 
  </code></pre>
  
  <p>As the result i need smt like this:</p>
  
  <pre><code>    |   category  |   material   | brand          | concat(category, brand) |
      ----------------------------------------------|-------------------------|
      |  engines    |  product *   | abb            | engines/abb             |
      |  engines    |  product *   | wika           | engines/wika            |
      |  engines    |  product *   | allen-bradley  | engines/allen-brandley  |
      |  engines    |  product *   | e+h            | engines/e+h             |
      |  drives     |  product *   | abb            | drives/abb              |
      |  drives     |  product *   | wika           | drives/wika             |  
      |  drives     |  product *   | allen-bradley  | drives/allen-bradley    |
      |  drives     |  product *   | e+h            | drives/e+h              |
  </code></pre>
  
  <p>If i use "group by" statement ( GROUP BY CONCAT() ), query time exceeds 10 seconds per 300 results and it doesnt make me happy.</p>
  
  <p>Anyone knows how to get unique values ​​within the group?</p>
  
  <p>UPD:</p>
  
  <pre><code>SELECT *, CONCAT(url_alias.alias, '/', LOWER(brand_alias.field_brand_path_value)) as real_url 
  FROM `taxonomy_term_hierarchy` as th0
  LEFT JOIN `taxonomy_term_hierarchy` as th1 ON th0.tid = th1.parent
  LEFT JOIN `taxonomy_term_hierarchy` as th2 ON th1.tid = th2.parent
  LEFT JOIN `taxonomy_term_hierarchy` as th3 ON th2.tid = th3.parent
  LEFT JOIN `taxonomy_term_hierarchy` as th4 ON th3.tid = th4.parent
  
  LEFT JOIN field_data_field_cat_reference as cat_reference ON    
  cat_reference.field_cat_reference_tid IN (th0.tid, th1.tid, th2.tid, th3.tid, th4.tid)
  LEFT JOIN node n ON cat_reference.entity_id = n.nid
  LEFT JOIN field_data_field_brand_reference as brand_reference ON n.nid = brand_reference.entity_id
  LEFT JOIN taxonomy_term_data as td_brand ON brand_reference.field_brand_reference_tid = td_brand.tid
  LEFT JOIN field_data_field_brand_path as brand_alias ON td_brand.tid = brand_alias.entity_id
  LEFT JOIN url_alias ON CONCAT('taxonomy/term/', th0.tid) = url_alias.source
  WHERE 1
  GROUP BY CONCAT(url_alias.alias, '/', LOWER(brand_alias.field_brand_path_value))
  </code></pre>