title: MAX(COUNT(*)) in Oracle
body: <p>I need to take MAX of my count but it does not work, 
  Oracle does not allow to deep aggregation.. </p>
  
  <p>this is results without MAX:</p>
  
  <pre><code>187 1   2
  187 3   1
  159 1   1
  159 3   1
  159 2   8
  188 2   9
  188 1   2
  188 3   1
  187 2   9
  
  
  
  select tt.token_id, tt.tag_variable_type_id, max(count(*)) as usage
  from tokens tt
  left outer join token_xref ttx on ttx.token_id = tt.token_id
  where tag_variable_type_id in (1, 2, 3) and ttx.template_id = 52
  group by tt.token_id, tt.tag_variable_type_id
  </code></pre>
  
  <p>I also tried to put it in having max(count(*)); but did not work</p>
  
  <p>Is there any way to get MAX of the COUNT() in one query and no inner select</p>
  
  <p>I expect after MAX</p>
  
  <pre><code>187 2 9
  159 2 8
  188 2 9
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>I have one more concern using analytic query 
  instead of just guess which row to exclude if I have duplicates</p>
  
  <pre><code>MIN(tag_variable_type_id) KEEP (DENSE_RANK LAST ORDER BY usage) AS tag_variable_type_id
  </code></pre>
  
  <p>I assigned to my variable type "Priority" using decode function, so my guess would be 
  <code>MIN(priority) KEEP (DENSE_RANK LAST ORDER BY usage) AS priority</code>, see  query</p>
  
  <p>But I am again loosing my tag_variable_type_id.. </p>
  
  <p>anyway to keep it? I decode it back, but might be better way</p>
  
  <pre><code> decode (MIN(priority) KEEP (DENSE_RANK LAST ORDER BY usage), 1,2, 2,1, 3,3) as typevar,
  
  
  
  select token_id,    
     MIN(priority) KEEP (DENSE_RANK LAST ORDER BY usage) AS priority,
     MAX(usage) AS usage 
  from ( select tt.token_id, tt.tag_variable_type_id,  
                decode(
                  tt.tag_variable_type_id,
                  1, 2, 
                  2, 1, 
                  3, 3  
                ) as priority,
                count(*) as usage 
         from tag_tokens tt 
         left outer join template_token_xref ttx on ttx.token_id = tt.token_id 
         where tag_variable_type_id in (1, 2, 3) and ttx.template_id = 52 
         group by tt.token_id, tt.tag_variable_type_id)
  group by token_id;
  </code></pre>