CODEWITHSUNDEEP
mysqlgroup-by
Green
Green

Reputation: 30785

Is it possible to select what values MySQL returns from a group?

A GROUP BY organizes records in groups. So a group can contain many rows and MySQL picks some arbitrary row of them. Here is comment to a question:

"mysql just returns the first row." - maybe this is how it works but it is not guaranteed. The documentation says: "The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.".

Is it possible to explicitly select which rows of a group MySQL should return? Something like in this pseudo code:

SELECT * FROM `visits` GROUP BY `visitor_id` 
(BUT PICK UP ONLY THOSE `visits` WHERE `venue` = 3)

Upvotes: 0

Views: 703

Answers (2)

axiac
axiac

Reputation: 72177

A GROUP BY organizes records in groups. So a group can contain many rows and MySQL picks some arbitrary row of them This is completely wrong..

The statement quoted above is completely wrong!

The GROUP BY clause generates one record from each group. It does not return rows from the group.

Each column of this record is computed independently of the other columns of the record. Each expression that is present in the SELECT clause of a GROUP BY query must follow one of these three rules:

  1. it is also present in the GROUP BY clause;
  2. the columns it uses are arguments of aggregate (GROUP BY) functions;
  3. it is a column that is functionally dependent of a column that is present in the GROUP BY clause.

A GROUP BY SQL query whose expressions in the SELECT clause do not follow these rules is invalid SQL.

As a side-effect, a query that reads SELECT * ... GROUP BY ... is invalid SQL. The only way for it to follow the rules above is to have the PK of the table in the GROUP BY clause. But in this case, the GROUP BY clause is useless because the PK in the GROUP BY forces each group to have exactly one row.

Before version 5.7.5, MySQL used to accept such invalid queries, but it reserved its right to return indeterminate results:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Since version 5.7.5, MySQL does not accept invalid SQL queries (and it is good it works this way). Other RDBMSes implements the standard more closely and never accepted such queries (or started rejected them long time ago).

Regarding your query, rethink and reformulate what exactly do you need, without trying to implement it (assume you never heard of SQL).

Upvotes: 0

RandomSeed
RandomSeed

Reputation: 29749

Is it possible to explicitly select which rows [plural] of a group MySQL should return?

No it is not, and it does not make sense because, as axiac points out:

The GROUP BY clause generates one [single] record from each group.

It is, however, possible to select which rows to retreive from a given category, for some conditions (example 1, example 2 -- but such queries usually do not involve a GROUP BYclause, or heavily twist its intended usage).

For your example, it looks like all you need is:

SELECT * FROM visits
WHERE venue = 3
ORDER BY visitor_id

Upvotes: 1

Related Questions