1

What row is returned by a group by in a query that has joins? (the default behavior in MySql)

*I looked around and saw several solutions for how to control which row comes back by joining (here). But what is the default behaviour? As mentioned else where the orderby doesn't seem to have an effect. I physically reversed the rows in one of the tables in question but that didn't seem to have an effect.

I'm trying to debug an existing horrific query in an existing system and multi-join group by query seems to pick the row that is required pretty much all the time. There is an edge case that I am trying to deal with.

Community
  • 1
  • 1
paullb
  • 4,293
  • 6
  • 37
  • 65

1 Answers1

2

https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

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.

you can get any matching row, so don't assume there is any default behaviour

Peter
  • 16,453
  • 8
  • 51
  • 77
  • I figured as much but it seems fairly consistent in it's select which has led to the issue not being detected until now. – paullb Apr 30 '15 at 09:19
  • ugh at group by abuse – pala_ Apr 30 '15 at 09:23
  • I'm just going to hold off giving you best answer for a little bit to leave the door open to someone who may have investigated and knows the internals. – paullb Apr 30 '15 at 09:27