Cristian,

I don't think you have an awful lot of choices. Maybe someone with a lot more SQL smarts than me can give you better ways to do it, but the bottom line as far as I'm concerned is that the way VFP7 and previous worked, was chance. By that, I mean that the fact that your 'group by' clause worked the way it did was just because VFP chose at random to do it that way. Any fields not included in the 'group by' clause are not really guaranteed to give you the information you necessarily want. Luckily, VFP7 did it the way you wanted, but like I said, I believe that it was just that - luck. Is there really any documentation stating that the non-grouped fields will necessarily return the last record, or the first record?

I don't remember who said it back somewhere in this thread, and I'm too lazy to look for it, but somebody used the phrase "the fields you don't care about" when referring to fields outside the 'group by' clause. I think that phrase is deadly accurate.

Alan

>Hi,
>
>I have a similar question.
>I used until now a select statement to retrieve first (or last) invoice to a client with something like:
>SELECT name, date, invoiceserial FROM invoices ;
> GROUP BY name ;
> ORDER BY name, date ASC (or DESC)
>wich always gets the first value from invoiceserial within name grup.
>Can't use a agregate on invoiceserial wich is not in secvential order by date and does not exist something like FIRST(invoiceserial)
>
>Should use somenthing like:
>SELECT name, MIN(date) FROM invoices GROUP BY name INTO CURSOR c1
>SELECT c1.name, c1.date, invoices.invoiceserial ;
> FROM c1 INNER JOIN invoices ;
> ON c1.name+DTOS(c1.date)=invoices.name+DTOS(invoices.date)
>but this will take twice the time for first and is not optimisable by rushmore
>How it's this in SQL server?
>I always get anoyed by Access in wich grup by clause is like new one in Vfp8
>
>>Unless you really want to group by a Memo field, which doesn't seem to make a lot of sense anyway, my suggestion is just forget about it. VFP8 doesn't support Memo fields in a 'group by' clause. But even using Set EngineBehavior 70 and putting the Memo field into the 'group by' wouldn't yield anything useful anyway. Why waste the resource on it?
>>
>>When you select 5 fields and group on only 3 of them the information returned in the other 2 may be nothing more than the contents of the last record. There is certainly no guarantee that the information in those 2 fields will have any relationship whatever to the information in the fields that were grouped on, other than probably being the contents of at least one of the records that was looked at in creating the group, so why bother with them at all?