MySQL returns fewer rows with GROUP BY statement

Question

I've got a MySQL database that stores image information. Right now it only has three rows stored in the database, and each row is associated with something like, for instance, a unique blog post via a key column.

As of right now, one "blog post key" has one image, and one has two images in the database.

When I run this query, MySQL returns all three rows.

SELECT `id`, `key`, `url`
FROM (`images`)
WHERE `key` = 'TpaS4G5h'
OR `key` = '78855e44'

However, when i add the GROUP BY statement I only get two rows... one for each key.

SELECT `id`, `key`, `url`
FROM (`images`)
WHERE `key` = 'TpaS4G5h'
OR `key` = '78855e44'
GROUP BY `key`

I'm sure there is a simple solution, but I don't know what it is... so any help would be really appreciated.

Thanks in advance!

Answer 1

You don't need a group by for this query! Group by groups identical values for the selected column into a single row.

Answer 2

GROUP BY groups all rows with the group by value into a single row, so it's doing exactly what it's supposed to. If you want the rows with the same key to be returned in consecutive rows, do an ORDER BY instead.

Answer 3

You are specifying two keys in the first query and returning three rows which means you have at list one duplicate in your key column. To validate this if you do a

  select distinct key from images 

you should only see two rows. This means if you group by this column all results that have this key in common will be rolled up in the result explaining the behavior you are seeing.

Answer 4

GROUP BY is used to "collapse" any rows which match the grouping criteria into a single row, so you can use the aggregate functions on them. SUM(), COUNT(), etc..

If you mean you want to group the records so that you that you can retrieve all the images associated with each key in a single fetchrow call, well, that's not what GROUP BY is for. You're trying to convert multi-row based data sets into a single row. If all you were trying to do was retrieve the IDs of those images, and do it in a single fetch per row, you could use something like

SELECT key, group_concat(id)
FROM images
WHERE key=XXX or key=YYY
GROUP BY key

That would give you a data set that looks like this:

key     group_concat(id)
------------------------
xxx     100,101
yyy     102,103

but that would require post-processing in your script to seperate those concat'd IDs into seperate numbers.