On May 5, 6:12 pm, Joachim Durchholz <j...@durchholz.org> wrote:
> Taras_96 schrieb:
>
> > I have the following query:
>
> > select * from teams group by teamid
>
> > multiple rows may have the same teamid. The above query ensures that
> > each row in the result set has a distinct teamid, which is the
> > behaviur I want, but I can't figure out *why* it works. I know that
> > the group by statement defines what you wish to aggregate over, but
> > the above query has no aggregate functions, so I'm not quite sure why
> > it returns distinct team ids. Does anyone know?
>
> Of a set of rows that have the same teamid, it will simply return the
> first one that it comes across.
> This behavior does not conform to the SQL standard AFAIK.
>
> I use it to debug my GROUP BY clauses. I compare the results with and
> without GROUP BY and check that it's indeed doing the kind of
> aggregation I wanted it to do.
>
> I never use it in production code. I'm not sure that ORDER BY would help
> selecting the row that I'd actually want; I suspect that sorting happens
> after selecting the rows (conceptually) - i.e. there might be cases
> where it works and cases where it doesn't, depending on what the
> optimizer did with the query (and it may choose different strategies,
> depending on query specifity, accuracy of row statistics, mysql version,
> and phase of the moon). So I end up with behaviour that I can't fully
> test and which might change with the next version of mysql anyway.
> Let me repeat: I never use it in production code.
> (This might change once the mysql docs give a strong guarantee to nail
> down the semantics of this kind of construct. It is possible that such a
> guarantee actually exists and I overlooked it... though I'd read very
> carefully anyway: such policies are more subject to change, and my
> impression is that mysql is moving towards better SQL conformance, not
> necessarily towards closing any gaps in nonstandard semantics.)
>
> Regards,
> Jo


Thanks Jo,

I had a feeling that it wasn't standard SQL as the results aren't
deterministic.

Taras