title: Subselect and group by
body: <p>I have two tables : </p>
  
  <p>main : id_main, field1, filter</p>
  
  <p>main_logs (50 millions lines) : auto inc, id_main, path</p>
  
  <p>I looking for the following results : 
  id_main, field1, most common path</p>
  
  <p>I tried the following query : </p>
  
  <pre><code>select id_main, 
    field1, 
    (select path, count(*) as cpt 
     from main_log 
     where main_log.id_main=main.id_main group by path order by cpt desc limit 1) 
  from main 
  where filter in (1,3,5);
  </code></pre>
  
  <p>Mysql return : Operand should contain 1 column(s)</p>
  
  <p>If I remove path, results are correct, but I miss the path value.</p>
  
  <pre><code>select id_main, 
    field1, 
    (select path, count(*) as cpt 
     from main_log 
     where main_log.id_main=main.id_main group by path order by cpt desc limit 1) 
  from main 
  where filter in (1,3,5);
  </code></pre>
  
  <p>I don't need the result of count(*) but I need it for the "order by"</p>
  
  <p>How can I write this query to get my results ?
  Thanks</p>
  
  <p><strong>main</strong></p>
  
  <pre><code>id_main     | field1    | filter
  1       | red       | 1
  2       | blue      | 3
  3       | pink      | 1
  </code></pre>
  
  <p><strong>main_logs</strong></p>
  
  <pre><code>autoinc     | id_main   | path
  1       | 1         | home1
  2       | 1         | home2
  3       | 1         | home2
  4       | 2         | house2
  5       | 2         | house7
  6       | 2         | house7
  7       | 3         | casee
  </code></pre>
  
  <p><strong>expected result</strong></p>
  
  <pre><code>id_main     | fields1   | most common path
  1       | red       | home2
  2       | blue      | house7
  3       | pink      | casee
  </code></pre>