Given the following table 'foo
'
ID | First Name | Last Name
----------------------------
67 John Smith
----------------------------
67 Bill Jacobs
What first_name
and last_name
will the following query return and why?
SELECT * FROM foo WHERE ID = 67 GROUP BY ID
The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Do not use the GROUP BY clause without an aggregate function. The rule is deprecated in the new SQL Server versions. The execution plan generated by using the GROUP BY is not different than the one of using DISTINCT.
To solve this problem in your query, just add both columns to the GROUP BY clause. Above two queries give always the same results for columns from and group_id , other columns (not included in the GROUP BY clause`) can be random.
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
To disable the MySQL GROUP BY extension and enable standard SQL behavior, enable the ONLY_FULL_GROUP_BY SQL mode. In this case, columns not named in the GROUP BY clause cannot be used in the select list or HAVING clause unless enclosed in an aggregate function.
SQL Having Clause is used to restrict the results returned by the GROUP BY clause. MYSQL GROUP BY Clause is used to collect data from multiple records and returned record set by one or more columns.
If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query.
MySQL chooses a row arbitrarily. In practice, commonly used MySQL storage engines return the values from the first row in the group, with respect to the physical storage.
create table foo (id serial primary key, category varchar(10));
insert into foo (category) values
('foo'), ('foo'), ('foo'), ('bar'), ('bar'), ('bar');
select * from foo group by category;
+----+----------+
| id | category |
+----+----------+
| 4 | bar |
| 1 | foo |
+----+----------+
Other folks are correct that MySQL allows you to run this query even though it has arbitrary and potentially misleading results. The SQL standard, and most other RDBMS vendors, disallow this kind of ambiguous GROUP BY query. This is called the Single-Value Rule: all columns in the select-list must be explicitly part of the GROUP BY criteria, or else inside an aggregate function, e.g. COUNT()
, MAX()
, etc.
MySQL supports a SQL mode ONLY_FULL_GROUP_BY
that makes MySQL return an error if you try to run a query that violates SQL standard semantics.
AFAIK, SQLite is the only other RDBMS that allows ambiguous columns in a grouped query. SQLite returns values from the last row in the group:
select * from foo group by category;
6|bar
3|foo
We can imagine queries that would not be ambiguous, yet still violate the SQL standard semantics.
SELECT foo.*, parent_of_foo.*
FROM foo JOIN parent_of_foo
ON (foo.parent_id = parent_of_foo.parent_id)
GROUP BY foo_id;
There's no logical way this could produce ambiguous results. Each row in foo gets its own group, if we GROUP BY the primary key of foo. So any column from foo can have only one value in the group. Even joining to another table referenced by a foreign key in foo can have only one value per group, if the groups are defined by the primary key of foo.
MySQL and SQLite trust you to design logically unambiguous queries. Formally, every column in the select-list must be a functional dependency of the columns in the GROUP BY criteria. If you don't adhere to this, it's your fault. :-)
Standard SQL is more strict and disallows some queries that could be unambiguous--probably because it would be too complex for the RDBMS to be sure in general.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With