MySQL GROUP BY returns only first row

Question

I have a table named forms with the following structure-

GROUP       | FORM       | FILEPATH
====================================
SomeGroup   | SomeForm1  | SomePath1
SomeGroup   | SomeForm2  | SomePath2
------------------------------------

I use the following query-

SELECT * FROM forms GROUP BY 'GROUP'

It returns only the first row-

GROUP       | FORM       | FILEPATH
====================================
SomeGroup   | SomeForm1  | SomePath1
------------------------------------

Shouldn't it return both (or all of it)? Or am I (possibly) wrong?

Answer 1

As the manual states:

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY:

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

In your case, MySQL is correctly performing the grouping operation, but (since you select all columns including those by which you are not grouping the query) gives you an indeterminate one record from each group.

Answer 2

It only returns one row, because the values of your GROUP column are the same ... that's basically how GROUP BY works.

Btw, when using GROUP BY it's good form to use aggregate functions for the other columns, such as COUNT(), MIN(), MAX(). In MySQL it usually returns the first row of each group if you just specify the column names; other databases will not like that though.

Answer 3

as far as mysql is concerned, I just solved my problem by hit & trial.

I had the same problem 10 minutes ago. I was using mysql statement something like this:

SELECT * FROM forms GROUP BY 'ID'; // returns only one row

However using the statement like the following would yeild same result:

SELECT ID FROM forms GROUP BY 'ID'; // returns only one row

The following was my solution:

SELECT ID FROM forms GROUP BY ID; // returns more than one row (with one column of field "ID") grouped by ID

or

SELECT * FROM forms GROUP BY ID; // returns more than one row (with columns of all fields) grouped by ID

or

SELECT * FROM forms GROUP BY `ID`; // returns more than one row (with columns of all fields) grouped by ID

Lesson: Donot use semicolon, i believe it does a stringtype search with colons. Remove colons from column name and it will group by its value. However you can use backtick escapes eg. ID