0

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)
Green
  • 28,742
  • 61
  • 158
  • 247
  • Possible duplicate of [MySQL - Control which row is returned by a group by](https://stackoverflow.com/questions/537223/mysql-control-which-row-is-returned-by-a-group-by) – Milan Velebit Sep 26 '17 at 09:24

2 Answers2

1

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
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
0

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).

Community
  • 1
  • 1
axiac
  • 68,258
  • 9
  • 99
  • 134
  • 1
    While technically correct, this does not answer the question. – RandomSeed Sep 26 '17 at 11:14
  • @RandomSeed It answers the question from the title and all the questions and issues from the question body. It doesn't provide a query to achieve what the OP needs because the OP didn't explain what they need. The pseudo-code query doesn't provide any rule to choose one row from each group and the question body also doesn't provide one. – axiac Sep 26 '17 at 11:27
  • Retracting downvote, because this answer is useful (although a bit besides the point IMHO). – RandomSeed Sep 26 '17 at 11:29