3

Such as this one:

SELECT id, count( * ) , company FROM jobs GROUP BY company

Where id is the primary key of jobs

markus
  • 40,136
  • 23
  • 97
  • 142
omg
  • 136,412
  • 142
  • 288
  • 348

1 Answers1

2

Similar question:

Why does MySQL allow "group by" queries WITHOUT aggregate functions?

This is MySQL specific, and is not ANSI standard SQL. Most other database engines do not allow columns on which is not being grouped or being run through an aggregate function.

It seems MySQL retains the value of the first row that matches the criteria.

The aggregate function that has this behaviour is FIRST(), and although MySQL implements it, this seems to be default behaviour for columns that are not grouped on, and which are not run through any other aggregate function.

In ANSI standard SQL you would do:

SELECT FIRST(jobtitle), company FROM jobs GROUP BY company;

Whereas in MySQL you could just (however the ANSI standard works just aswell):

SELECT jobtitle, company FROM jobs GROUP BY company;
Community
  • 1
  • 1
Yannick Motton
  • 34,761
  • 4
  • 39
  • 55
  • 2
    FWIW, SQLite also supports this non-standard behavior of group-by, but SQLite returns the value from the *last* matching row in the group. – Bill Karwin Sep 20 '09 at 15:51
  • This answer is wrong: MySql does not support the FIRST keyword (http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html). Furthermore, a random matching value is selected - not necessarily the value in the first row that appears in the group. – Breck Fresen Mar 13 '11 at 22:35