0

Since mysql does not enforce the Single-Value Rule (See: https://stackoverflow.com/a/1646121/1688441) does a derived table with an order by guarantee which row values will be displayed? This is for columns not in an aggregate function and not in the group by.

I was looking at the question (MySQL GROUP BY behavior) after having commented on and answered the question (https://stackoverflow.com/a/24653572/1688441) .

I don't agree with the accepted answer, but realized that a possible improved upon answer would be:

SELECT * FROM 
(SELECT * FROM tbl order by timestamp) as tb2
GROUP BY userID;

http://sqlfiddle.com/#!2/4b475/18

Is this correct though or will mysql still decide arbitrarily which row values will be displayed?

ekad
  • 14,436
  • 26
  • 44
  • 46
Menelaos
  • 23,508
  • 18
  • 90
  • 155
  • As a note: the question you have referred to does not have an accepted answer. – Gordon Linoff Jul 14 '14 at 00:26
  • @Gordon Linoff I was referring to http://stackoverflow.com/a/24653572/1688441 which you had also answered. It got my thinking on group by behavior. :-) – Menelaos Jul 14 '14 at 00:28
  • 1
    As a note. I do not keep up with MySQL development. But, they deprecated the automatic ordering for `group by` in version 5.6. That suggests that they are thinking about adding other algorithms to MySQL, and these might specifically change the behavior of `group by` in this context. – Gordon Linoff Jul 14 '14 at 00:36

1 Answers1

3

This query:

SELECT *
FROM (SELECT * FROM tbl order by timestamp) as tb2
GROUP BY userID;

Relies on a MySQL group by extension, which is documented here. You are specifically relying on the fact that all the columns come from the same row and the first one encountered. MySQL specifically warns against making this assumption:

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.

So, you cannot depend on this behavior. It is easy enough to work around. Here is an example query:

select t.*
from tbl t
where not exists (select 1 from tbl t2 where t2.userid = t.userid and t2.timestamp > t.timestamp)

With an index on tbl(userid, timestamp) this may even work faster. MySQL does a notoriously poor job of optimizing aggregations.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    "The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.". Well that pretty much settles it :-). Thanks. – Menelaos Jul 14 '14 at 00:30