Which record will GROUP BY choose in SQL

Question

If I have an SQL query like this one:

SELECT playerno,town
FROM players
GROUP BY town

In this case, the server will return lets say one town, and for playerno, it will return one value, even if there were multiple values in the database.

My question is, which value will be chosen? Is the choice made randomly, or will it take the first result, or something else will happen? Thank you.

Answer 1

The MySQL documentation is quite explicit on what happens in the section on "Group By Extentions".

For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY:

  SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

So, the answer to your question is indeterminate. The values come from indeterminate rows and you should not depend on the values coming from any particular row.

Answer 2

If you don't tell the engine what to do , it decides for itself. Its depending on the engine some will take the first one, others will take the last one, and some give an error. You can control this by using one of those functions:

1. first(fieldname)
2. last(fieldname)
3. min(fieldname)
4. max(fieldname

or even take an avarge or sum: by using su