CODEWITHSUNDEEP
mysql
Oto Shavadze
Oto Shavadze

Reputation: 42863

GROUP BY clause with non aggregate functions

Why mysql allows use non aggregate functions with GROUP BY clause ?

For example, this query works fine:

SELECT col, CHAR_LENGTH(col) FROM table
GROUP BY col

There is acceptable using querys like this ?

Upvotes: 1

Views: 2900

Answers (3)

piotrpo
piotrpo

Reputation: 12636

MySQL has some "improvements" and tries to run and return result from invalid queries, in example like yours every good RDBMS should throw syntax error, but MySQL will run it, group the result by col and put value of randomly chosen row into second column.

Upvotes: 1

fthiella
fthiella

Reputation: 49089

Sometimes is quite acceptable. Your query, written in more standard SQL, would be something like:

SELECT col, CHAR_LENGTH(col)
FROM (SELECT col FROM table GROUP BY col) c

or as:

SELECT col, MAX(CHAR_LENGTH(col))
FROM table
GROUP BY col

using non aggregate functions you can simplify the query a little bit, but the query would be a little more difficult to read.

It could also be useful when you are sure that all non aggregated columns share the same value:

SELECT id, name, surname
FROM table
GROUP BY id
HAVING COUNT(*)=1

or when it doesn't matter which value you need to return:

SELECT id, name
FROM table
GROUP BY id

will return a single name associated to that id (probably the first name encountered, but we can't be sure which one is the first, order by doesn't help here...). Be warned that if you want to select multiple non aggregated columns:

SELECT id, name, surname
FROM table
GROUP BY id

we have no guarantees that the name and surname returned will belong to the same row.

I would prefer not to use this extension, unless you are 100% sure of why you are using it.

Upvotes: 1

jpmc26
jpmc26

Reputation: 29974

If I'm guessing correctly about what you want to do, DISTINCT is a better choice:

SELECT DISTINCT col, CHAR_LENGTH(col) FROM table;

It more clearly indicates the readers what you're trying to accomplish.

Here is a SQLFiddle.

Upvotes: 0

Related Questions