title: Why is this group by query not working?
body: <p>I have a query that is not grouping properly and returning the wrong results and I can't figure out what the problem is.  </p>
  
  <p>The query is shown below.  FYI - It's not obvious in it's current form why I need the group by because I've removed all other parts of the query to get to the most basic form where I see a problem.</p>
  
  <pre><code>SELECT * FROM (
    SELECT *
    FROM notifications n
    WHERE 1
    --  and group_id = '5b35c8eb075881f8bbdfbcb36b052aa7'
    GROUP BY `from`
  ) t 
  WHERE group_id = '5b35c8eb075881f8bbdfbcb36b052aa7'
  </code></pre>
  
  <p>The problem is that when I use put the where on the inside subquery (currently commented out), for this case, I end up with 4 results.   Each of the 4 results have a different "from" value so should be listed separately.  When I put the where on the outside of the subquery I end up with 3 results.  </p>
  
  <p>For completeness the table definition is:</p>
  
  <pre><code>CREATE TABLE `notifications` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mem_id` int(10) unsigned DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `from` varchar(255) DEFAULT NULL,
  `entry_id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `table_id` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `emailed` tinyint(1) DEFAULT NULL,
  `read` tinyint(1) NOT NULL,
  `group_id` char(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `mem_id` (`mem_id`),
  KEY `created_at` (`created_at`),
  KEY `entry_id` (`entry_id`),
  KEY `parent_id` (`parent_id`),
  KEY `group_id` (`group_id`)
  )
  </code></pre>
  
  <p>Any ideas what could cause this?  I'm completely stumped.  At this point I'm ready to attribute it to some bug in mysql but that also seems unlikely.</p>
  
  <hr>
  
  <h2>Update</h2>
  
  <p>I wasn't clear by what I meant by "wrong results"  There were 7 records in the data set with this group_id.  There were 2 records with a unique "from" and 5 more records with 2 other "from" ids (one had 3 records, one had 2).  </p>
  
  <p>Doing the where for the group by on the inside resulted in in the 4 records that I wanted.  I don't care about which row was selected as the result because I'm doing other sums/counts which I excluded from the example because it wasn't directly relevant to the problem.</p>
  
  <p>If I do the where on the outer group by one of the two records with a single "from" did not return at all.<br>
  I'll try to update with a sqlfiddle (didn't know about that!) - the issue is that this database I was testing on is wiped daily so I don't have the original data, I'll see if I can reproduce.  </p>
  
  <h2>update #2</h2>
  
  <p>I noticed that in my questions, I've been referring to inner and outer group by - the group by is always on the inner query it's just where the "where" is.  I've tried to adjust the phrasing.  Again, it's not immediately obvious why I care about the location of the where - but in my final use case, I need the selection to happen on the outside (I'm building a count of notifications that are read/unread and I need a count both per member and total per message - eg the group_id) </p>
  
  <p>sqlfiddle: <a href="http://www.sqlfiddle.com/#!2/7d746/5" rel="nofollow">http://www.sqlfiddle.com/#!2/7d746/5</a>  </p>
  
  <p>screenshot of query with inner where:<a href="https://www.evernote.com/shard/s48/sh/e355e96e-e48d-4550-bbaf-ffb18bc0bb9c/08e2454867e00e3a05535303429748f1" rel="nofollow">https://www.evernote.com/shard/s48/sh/e355e96e-e48d-4550-bbaf-ffb18bc0bb9c/08e2454867e00e3a05535303429748f1</a></p>
  
  <p>screenshot of query with outer where:<a href="https://www.evernote.com/shard/s48/sh/60b10427-e417-4196-8b92-7d6d8031d21e/c779bc9c46d23472983ac6fa0d25e42d" rel="nofollow">https://www.evernote.com/shard/s48/sh/60b10427-e417-4196-8b92-7d6d8031d21e/c779bc9c46d23472983ac6fa0d25e42d</a></p>
  
  <p>With the sqlfiddle I get back 4 results each time!  Which leads me more to think it's a server issue.  We're running MySQL 5.5.28-29.2 Percona Server (GPL), Release rel29.2, Revision 360</p>