This is in 9 and the MAX on the dates does seem logical to me. He is asking : Within each group ( he groups on three fields - the only fields that are not aggregates ) what is the maximum date in that column. I don't see a logical problem with that. ( I understand the example you gave that in 7 you could not group that field but the answer would be meaningless ) But in this case he really does want to know what is the most recent date in that group.

In this case, he is maxing the memo fields for exactly the reason you give as a work around for the 9 group by rules. All the memos within a group have the same data. He wants that data in the result set. I don't think the code he gave me works, but I understand now what he is trying to do. I guess he could have done this by setting enginebehavior 7 and just not maxing the memo field.

When he sends me code that works, I'll let you know how we solved it. I suggested to him that he do his select without the memos and then do another select to add the memo fields for each group. ( just as you did )

He's checking his code now <g>

What was the percentage in the election in Izmir between AKP and CHP ? ( I suspect it was a little different than the national numbers <s> )

>>
>>Sagol ! ( sorry, still can't figure out the alt combo for yumushak g )
>>
>>You a quite correct - removing the fields involving memos let me run the SELECT.
>>
>>I can't figure out why he's maxing the memo fields - I didn't even know you could CAST that.
>>As to WHY it was written this way I hope the guy who wrote it will be able to explain. His mind is strange and wonderful and there is generally "method to his madness" ( deyim - "a reason for something that seems crazy" )
>>
>>At least now I can see what the other results look like. He is very good at writing very complicated stuff ( I am taking this app over from him ) and most of the time I don't know if he is wrong or I'm just stupid <s>
>>,CAST(NULL AS Memo) AS HazardList ;
>>
>>Definitely throws an error that is invalid on that data type
>>
>>I think in this case he just needed a blank memo in each record so he could stuff in some other information ( I did it the old fashioned way doing a cartesian join to a dummy table with a memo field ) Not sure what the other ones are for.
>>
>>
>>Thanks again.
>
>I don't use Turkish chars even on Turkish forums:)
>Max() workaround is used to mimic VFP7's group by in a way. VFP9 wouldn't allow this:
>
>

>select cust_id, company, sum(maxordamt) group by 1
>

>
>VFP7 accepts though it's buggy to accept it. So the workaround is to make the company an aggragate:
>

>select cust_id, max(company) as company, sum(maxordamt) group by 1
>

>
>Consider data in orders and a group by that works in 7:
>
>select cust_id, order_date, sum(order_net) from orders group by 1
>
>Where does date come from? It's the last physically entered date per customer. In other words you could pick up any value just to populate that column. It is a logical bug. Coincidentally (as the data entered orderly on date and VFP data has a sense of physical entry order of data):
>
>select cust_id, max(order_date), sum(order_net) from orders group by 1
>
>works and gives the same result in VFP9. Just scramble the data rows a bit and you'd get different result in VFP7, while in VFP9 the result is consistent dure to aggregation max().
>
>Anyway I'd rewrite your SQL in 2 steps, one getting the real grouping data and one another joining the result for other fields (may not be easy to formulate).
>
>Cetin