title: Fetch multiple max values for mutually exclusive columns into a single row
body: <p>I have a table say <code>call_memmo</code> which is having a column <code>chance_no</code> of <code>int</code> type. There are other two columns <code>is_group1</code> and <code>is_group2</code> both are <code>tinyint</code> type and are mutually exclusive. So I want to fetch <code>max(chance_no)</code> for <code>is_group1=1</code> and <code>is_group2=1</code> in a single query.</p>
  
  <p>I tried this query</p>
  
  <pre><code>select if(is_group1 &lt;&gt; 0, max(chance_no), 0) as max_chance_group1,
         if(is_group2 &lt;&gt; 0, max(chance_no), 0) as max_chance_group2
  from call_memmo
  </code></pre>
  
  <p>which gave result as</p>
  
  <pre><code>max_chance_group1   max_chance_group2
                  0                   3
  </code></pre>
  
  <p>The output is wrong because <code>max_chance_group1</code> should come as <code>2</code>.</p>
  
  <p>Then I tried this query </p>
  
  <pre><code>select (select max(chance_no) from call_memmo where is_group1 = 1) as max_chance_group1, 
         (select max(chance_no) from call_memmo where is_group2 = 1) as max_chance_group2
  
  from call_memmo group by is_group1, is_group2
  </code></pre>
  
  <p>This is giving output correctly but multiple records are coming</p>
  
  <pre><code>max_chance_group1   max_chance_group2
                  2                   3
                  2                   3
  </code></pre>
  
  <p>I know that the query I used is not proper, so can anybody help me with a better way and get output in single row so that I can skip iteration?</p>