>Hi Everyone,
>
>Just a quick update on this - one of the code changes required by this has not been easy - still working on it. The problem comes in that there are multiple rows of data that are "just about" the same but because of a slight difference in one field, the grouping no longer works correctly. Here is the situation if anyone can help:
>
>1) one "file" (case) has multiple contacts connected to it - lawyers that is
>2) one of these might be marked as the "lead" lawyer (but now always)
>3) when doing a printout, they want the file details to show along with the lead lawyers name
>
>In the past, how I handled this was to sort the data by File_ID and then "Lead" lawyer last and then do a group by something like (with a bit of pseudo code):
>
>

>
>SELECT <fields from Files plus couple of fields from child table eg.
>   Lawyer_ID, LeadLawyer) > ;
>  FROM Files JOIN Contacts ON Files.File_ID == Contacts.File_ID ;
>  ORDER BY Files.File_ID, LeadLawyer DESC ;
>  INTO CURSOR FilesSorted
>
>SELECT * ;
>   FROM FilesSorted ;
>   GROUP BY File_ID ;
>   INTO CURSOR ...
>
>

>
>This worked and I got the "lead lawyer" on the file to show without a lot of work. But now with the new and improved GROUP BY, I am having trouble doing the above. I was able to wrap LeadLawyer in MAX() to get a flag to tell me if the file had a LeadLawyer on it, but I don't think I can associate that "LeadLawyer" with the lawyer's Lawyer_ID; that is, I cannot wrap Lawyer_ID in MAX().
>
>So far, the only thing I can think of is to write a function that looks up the contacts for the file and throws them into an array or something and then returns the lead lawyer by looking through the array.
>
>Any other way to do this? BTW, the record count is pretty high (a few thousand for a law firm and they want the results on screen) and so I anticipate that running a UDF a few thousand times is really going to slow loading the grid.
>
>Albert

this is not equal, but...

SELECT <fields from Files plus couple of fields from child table eg.   
   Lawyer_ID, LeadLawyer) > ;
  FROM Files ;
  JOIN Contacts ON Files.File_ID == Contacts.File_ID ;
  JOIN (SELECT File_ID,MAX(LeadLawyer) LeadLawyer FROM Contacts GROUP BY 1) MAXLL;
  ON Contacts.File_ID=MAXLL.File_ID AND Contacts.LeadLawyer=MAXLL.LeadLawyer);
  ORDER BY Files.File_ID;
  INTO CURSOR ...