Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL GROUP BY behavior

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
like image 356
Matthew Avatar asked Oct 29 '09 19:10

Matthew


People also ask

How do I group data in MySQL?

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.

Can we use GROUP BY without aggregate function MySQL?

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.

Why GROUP BY is not working in MySQL?

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.

Can we use SELECT * with GROUP BY?

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.

What is the use of group by in MySQL?

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.

How do I disable the MySQL Group by extension?

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.

What is the difference between MySQL HAVING clause and GROUP BY clause?

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.

What happens if only_full_group_by is disabled in MySQL?

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.


1 Answers

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.

like image 69
Bill Karwin Avatar answered Oct 19 '22 03:10

Bill Karwin