StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy is this group by query not working?
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload