SEO

Hi all,

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?

Thanks

Taras

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

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