CODEWITHSUNDEEP
mysqlsqlgroup-by
bielrv
bielrv

Reputation: 13

GROUP BY clause in MySQL groups records with different values

MySQL GROUP BY clause groups records even when they have different values.

However I would like it to as with DB2 SQL so that if records not contain exactly the same information they are not grouped.

Currently in MySQL for:

id Name

A Amanda

A Ana

the Group by id would return 1 record randomly (unless aggregation clauses used of course)

However in DB2 SQL the same Group by id would not group those: returning 2 records and never doing such a thing as picking randomly one of the values when grouping without using aggregation functions.

Upvotes: 0

Views: 1691

Answers (3)

ghn
ghn

Reputation: 119

I do not think there is an automated way to do this but using 

GROUP BY id, name

Would give you the solution you are looking for

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

First, id is a bad name for a column that is not the primary key of a table. But that is not relevant to your question.

This query:

select id, name
from t
group by id;

returns an error in almost any database other than MySQL. The problem is that name is not in the group by and is not the argument of an aggregation function. The failure is ANSI-standard behavior, not honored by MySQL.

A typical way to write the query is:

select id, max(name)
from t
group by id;

This should work in all databases (assuming name is not some obscure type where max() doesn't work).

Or, if you want each name, then:

select id, name
from t
group by id, name;

or the simpler:

select distinct id, name
from t;

In MySQL, you can get the ANSI standard behavior by setting ONLY_FULL_GROUP_BY for the database/session. MySQL will then return an error, as DB2 does in this case.

The most recent versions of MySQL have ONLY_FULL_GROUP_BY set by default.

Upvotes: 1

abr
abr

Reputation: 2129

Group by in mysql will group the records according to the set fields. Think of it as: It gets one and the others will not show up. It has uses, for example, to count how many times that ID is repeated on the table:

select count(id), id from table group by id

You can, however, to achieve your purpose, group by multiple fields, something among the lines of:

select * from table group by id, name

Upvotes: 0

Related Questions