>Hello.
>An excerpt from VFP8 Help:
>"The GROUP BY clause must list EVERY field in the SELECT list, except for fields contained in an aggregate function, such as the COUNT( ) function. In addition, the GROUP BY clause must also list EVERY field in a HAVING clause, except for fields contained in an aggregate function. "
>
>Let's see...
>
CREATE CURSOR test (pk c(32), nr n(3), txt M)
>SELECT * from test GROUP BY 1
>SELECT * from test GROUP BY 1,2
>SELECT pk, nr, txt FROM test GROUP BY pk, nr
>All of the SELECTs produce "GROUP BY clause is missing or invalid" result. You basically can't Group the cursor.
>On the other hand
>
SELECT pk, nr, 000 as cn FROM test GROUP BY pk, nr, cn
>SELECT pk, nr, 000 as cn FROM test GROUP BY pk, nr
>Is fine.
>
>I find the GROUP BY innovation counterproductive. In VFP7 it worked like that (snap). Now i have to change not only the code of my Selects but also the code around them.
>"The GROUP BY clause must list EVERY field..." - what is the reasoning behind this change?
>To make Foxpro more in line with SQL server?
>- Doesn't seem like that.
>What do you think?
Kamil,
"To make Foxpro more in line with SQL server?" But not only that. It's the correct logical way.
SELECT pk, nr, 000 as cn FROM test GROUP BY pk, nr
should work because cn is a constant value not affecting the result.
Consider this :
Select cust_id, order_date, sum(order_amt) from orders group by cust_id
If it worked (as did in pre 8) what should be the order_date value ? Pre 8 simply returned the last physically entered one but that's not correct (and no correct selection I can think of - for a date I might be asking for latest entry datewise but who knows and it might be different from physical entry order).
Cetin
