title: Group only certain rows with GROUP BY
body: <p><strong>SCHEMA</strong></p>
  
  <p>I have the following set-up in MySQL database:</p>
  
  <pre><code>CREATE TABLE items (
    id SERIAL,
    name VARCHAR(100),
    group_id INT,
    price DECIMAL(10,2),
    KEY items_group_id_idx (group_id),
    PRIMARY KEY (id)
  );
  
  INSERT INTO items VALUES 
  (1, 'Item A', NULL, 10),
  (2, 'Item B', NULL, 20),
  (3, 'Item C', NULL, 30),
  (4, 'Item D', 1,    40),
  (5, 'Item E', 2,    50),
  (6, 'Item F', 2,    60),
  (7, 'Item G', 2,    70);
  </code></pre>
  
  <p><strong>PROBLEM</strong></p>
  
  <blockquote>
    <p>I need to select:</p>
    
    <ul>
    <li><strong>All</strong> items with <code>group_id</code> that has <code>NULL</code> value, <strong>and</strong></li>
    <li><strong>One</strong> item from each group identified by <code>group_id</code> having the <strong>lowest</strong> price.</li>
    </ul>
  </blockquote>
  
  <p><strong>EXPECTED RESULTS</strong></p>
  
  <pre><code>+----+--------+----------+-------+
  | id | name   | group_id | price |
  +----+--------+----------+-------+
  |  1 | Item A |     NULL | 10.00 | 
  |  2 | Item B |     NULL | 20.00 | 
  |  3 | Item C |     NULL | 30.00 | 
  |  4 | Item D |        1 | 40.00 | 
  |  5 | Item E |        2 | 50.00 | 
  +----+--------+----------+-------+
  </code></pre>
  
  <p><strong>POSSIBLE SOLUTION 1:</strong> Two queries with <code>UNION ALL</code></p>
  
  <pre><code>SELECT id, name, group_id, price FROM items
  WHERE group_id IS NULL
  UNION ALL
  SELECT id, name, MIN(price) FROM items
  WHERE group_id IS NOT NULL
  GROUP BY group_id;
  
  /* EXPLAIN */
  +----+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------+
  | id | select_type  | table      | type | possible_keys      | key                | key_len | ref   | rows | Extra                                        |
  +----+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------+
  |  1 | PRIMARY      | items      | ref  | items_group_id_idx | items_group_id_idx | 5       | const |    3 | Using where                                  | 
  |  2 | UNION        | items      | ALL  | items_group_id_idx | NULL               | NULL    | NULL  |    7 | Using where; Using temporary; Using filesort | 
  | NULL | UNION RESULT | &lt;union1,2&gt; | ALL  | NULL               | NULL               | NULL    | NULL  | NULL |                                              | 
  +----+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------------------------------------------+
  </code></pre>
  
  <p>However it is undesirable to have two queries since there will be more complex condition in <code>WHERE</code> clause and I would need to sort the final results. </p>
  
  <p><strong>POSSIBLE SOLUTION 2:</strong> <code>GROUP BY</code> on expression (<a href="https://stackoverflow.com/a/18883400/3549014">reference</a>)</p>
  
  <pre><code>SELECT id, name, group_id, MIN(price) FROM items
  GROUP BY CASE WHEN group_id IS NOT NULL THEN group_id ELSE RAND() END;
  
  /* EXPLAIN */
  +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
  +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
  |  1 | SIMPLE      | items | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using temporary; Using filesort | 
  +----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
  </code></pre>
  
  <p>Solution 2 seems to be faster and simple to use but I'm wondering whether there is a better approach in terms of performance.</p>
  
  <p><strong>UPDATE</strong>:</p>
  
  <p>According to documentation referenced by <a href="https://stackoverflow.com/questions/36010981/group-only-certain-rows-with-group-by/36011093?noredirect=1#comment59674237_36010981">@axiac</a>, this query is illegal in SQL92 and earlier and may work in MySQL only.</p>