The DBISAM 4r2007 reference manual says the following about GROUP BY clause and SELECT
columns:

When a GROUP BY clause is used, all table columns in the SELECT clause of the query must
meet at least one of
the following criteria, or it cannot be included in the SELECT clause:
•   Be in the GROUP BY clause of the query.
•   Be the subject of an aggregate function.

This is not enforced, so I would like to know what happens to non-aggregate, non-grouped
columns used in the SELECT statement.  Where do the values come from?  Are they the first
or last values in the group, given the ORDER BY sorting, or perhaps the order of records
in the tables queried?  Are they undefined?

(I'm not asking out of curiosity; I'm wondering whether I can use non-aggregate,
non-grouped columns with GROUP BY to save work.)

Thanks,
Bill

"Bill Root" <esnewsgroup at ascendis dot com> wrote in message
news:C0BCCF5F-3D09-462D-B7DF-BB53D158F9C5@news.elevatesoft.com...
> The DBISAM 4r2007 reference manual says the following about GROUP BY
> clause and SELECT
> columns:
>
> When a GROUP BY clause is used, all table columns in the SELECT clause of
> the query must
> meet at least one of
> the following criteria, or it cannot be included in the SELECT clause:
> .   Be in the GROUP BY clause of the query.
> .   Be the subject of an aggregate function.
>
> This is not enforced, so I would like to know what happens to
> non-aggregate, non-grouped
> columns used in the SELECT statement.  Where do the values come from?  Are
> they the first
> or last values in the group, given the ORDER BY sorting, or perhaps the
> order of records
> in the tables queried?  Are they undefined?
>
> (I'm not asking out of curiosity; I'm wondering whether I can use
> non-aggregate,
> non-grouped columns with GROUP BY to save work.)
>

I would not use them, simply because Elevate might one day decide to enforce
the rule above (other databases do) and also because the result is by
definition ambiguous. What "works" (i.e. produces the expected result) on
one version of DBISAM might stop working on  the next version. And you will
not get an SQL error, simply bad results.

Having said that, if you know for sure that the result can not be ambiguous,
then I guess it is OK to select other fields. Tim has indicated before that
he will probably never change the rules, since it would break too much
existing SQL.

Robert

Thanks, Robert.  If it's not supposed to work in SQL then I won't use it, no matter how
convenient it is.  I know better.

-Bill