>>Old Visual FoxPro can do this, but it is out of all SQL standards.
>
>Opinions differ.
>
>
>this is not a opinion, it is a fact.
>You can say that when
group by have a fields list it's a Primary key>the ambiguity is resolved,
But I have no ambiguity here, that's the point. I'm practically forced to do a join on a new view because of these rules, even though I'm grouping by PK and having an aggregate function on a count from a joined table - and I'm not selecting any fields from that table.
>but i do not known a database package that use this set property directly;
>then the only way is to grouping memo or aggregate memo,
>- grouping ( that is sorting ) is hard because the memo do not have a short limit,
>- aggregate can to be MAX(),MIN(),FIRST(),LAST(),
>for MAX() and MIN() is need sorting, then the way is cut,
>only FIRST and LAST are possible.
I've had a number of situations where I really didn't care which of the values for the columns which were not in the Group By nor in an aggregate function. These were usually a result of a previous query, values from a lookup table, so they were already equal for all the values of the fields I grouped by. OK, still no problem, because I can do a little drag and drop and add them to the Group By. It's this situation where I can't have a memo and an aggregate function in the same select, which forces me to perform acrobatics around it, and generally annoys me.
>Before VFP8, VFP implement LAST() implicitly ,
>but AFAIK only MS Access implement FIRST() explicitly.
>
>In fact the FIRST() and LAST() is very usefull on general case,
>but some mysterious reason they are not implemented.
I agree. Too bad we don't have them. I've just added these as a Wish List entry #1476 - feel free to add comments there.