Hi Tamar,
I see the problem clearly regarding DISTINCT. Not so with GROUP BY, but I take your word for it.
I can only guess that the incidence of actual usage of memo fields with distinct must be low (which makes some sense to me as a general rule).
The GROUP BY would seem to be far more common usage, yet I can't (doesn't mean there weren't any) recollect this being discussed here to any extent.
In any case, I would have PREFERRED a different implementation. But we get what we get.
cheers
>>That there were problems in prior VFPx releases in this area (incorrect results of DISTINCT and incomplete GROUP BY).
>
>These are simple to see. I'm writing this off the top of my head, so the code is untested, but here's the gist.
>
>First, for DISTINCT:
>
>
>CREATE CURSOR HasMemo1 (iFK I, mNotes M)
>INSERT INTO HasMemo VALUES (1, "This is the first note")
>INSERT INTO HasMemo VALUES (2, "This is the second note")
>
>CREATE CURSOR HasMemo2 (iFK I, mNotes M)
>INSERT INTO HasMemo VALUES (1, "This is a different note")
>INSERT INTO HasMemo VALUES (2, "So is this")
>
>SELECT * FROM HasMemo1 ;
>UNION ;
>SELECT * FROM HasMemo2 ;
>INTO CURSOR Result
>
>
>You get only two records in the result, not four, because the memos aren't compared.
>
>For GROUP BY, consider this one:
>
>
>OPEN DATABASE HOME(2)+"TasTrade\Data\TasTrade"
>SELECT customer_id, MAX(order_date), freight ;
> FROM orders ;
> GROUP BY 1 ;
> INTO CURSOR result
>
>
>The values for Freight don't match up with the most recent order.
>
>Tamar