GROUP BY vs DISTINCT

[David W. Fenton]

You are also missing that GROUP BY WITH AGGREGATE can use the
similar query plan than the one used by DISTINCT and GROUP BY
without aggregate (as you can check my 'claim' using MS SLQ
Server), where the aggregates are done for a small fraction (less
than half of one percent of the whole query execution). Jet is
inefficient in both cases, I mean, with or without aggregate.

Have you tested that Jet's GROUP BY for aggregates has a performance
problem?

I NEVER said that result that are identical implies the execution
plan should be the same. I said LOGICALLY EQIVALENT description
SHOULD be optimized the same way. And DISTINCT and GROUP BY
without aggregate ARE logically equivalent descriptions.

But they *aren't* logically equivalent.

I'm done here.

 

[Michel Walsh]

1- Yes, they are as slow as without aggregate, while they should run in a
time almost equivalent as DISTINCT, for simple aggregate (that is, not for
overly complex arithmetic expression, VBA function, sub-query, ... don't add
words I don't say).

2- They are equivalent, as mentioned by Keith Hare, Convenor of the
International SQL Standards. I try to find a public reference on the web at
the moment, no success yet, but they are logically equivalent. And you are
wrong.

3- Allen Browne reports a case when DISTINCT, in JET, is buggy, on the
reported case, and YOU HAVE TO use GROUP BY, without aggregate, to really
get a list of "distinct" values: http://allenbrowne.com/bug-12.html


Vanderghast, Access MVP

 

[David Cox]

Er, you don't have to edit SQL to do that.

My apologies to the group. It is possible to specify Unique records in the
properties window of a query, brought up by left clicking in the design
window.

I do not like this implementation, so I had never used it, and forgot it
existed.
a} This is quite a different query with the option selected, but it looks
the same in the design window.
b} It is potentially misleading. Unique means to me records that are not
duplicated, I would naturally tend to interpret distinct the same way. I
would prefer to see the word FIRST, or FIRSTOFGROUP used for this feature.
Given the way it is I would find it easier to explain to a newbie that
Access coceptually uses the first value it finds in a group by default, and
expect it to be actually optimised to work that way.