title: oracle group related subquery
body: <p>Helo,
  I have 2 simple tables and I must make one query which group row "GRO" from PROD and return "gro" which is used most often by coid. I try use subquery but i have error, I dont know how to transform this query to work.</p>
  
  <p>Please help me :)</p>
  
  <p>I use Oracle SQL . </p>
  
  <p><strong>TABLE CO</strong>        </p>
  
  <pre><code>ID |name|param|
  ---+----+-----+
  1  |AA  | X   |
  2  |BB  | X   |
  3  |CC  | X   |
  4  |DD  | X   |
  5  |EE  |     |
  </code></pre>
  
  <p><strong>Table PROD</strong></p>
  
  <pre><code>id| coid |gro
  --+------+------
  1 |  1   | a
  2 |  1   | a
  3 |  1   | b
  4 |  1   | c
  5 |  1   | d
  6 |  2   | b
  7 |  2   | c
  8 |  2   | c
  9 |  3   | a
  10|  3   | a
  11|  4   | b
  12|  4   | b
  13|  4   | b
  14|  4   | a
  15|  1   | a
  </code></pre>
  
  <p><strong>Result</strong></p>
  
  <pre><code>ID |name|best_gro|
  ---+----+--------+
  1  |AA  | a      |
  2  |BB  | c      |
  3  |CC  | a      |
  4  |DD  | b      |
  </code></pre>
  
  <p><strong>My Query</strong></p>
  
  <pre><code>select c.id, c.name,
  (
      select gro from(
      SELECT  GRO, count(GRO) as m FROM HR.PROD pro
        where coid = c.id        --&lt;------ ERROR 
      group by gro order by m desc
      ) where rownum &lt;=1
  ) as best_gro
  
  from HR.co c 
  where c.param = 'X'
  </code></pre>