>>Hi: Using foxpro, I have a table as follows:
>>
>>ID Name
>>------ --------------------
>>1234 Fred
>>5678 Robin
>>2345 Bertha
>>2345 Bertha Smith
>>5678 Robin Jackson
>>
>>I am trying to figure out an SQL query that will identify the people with the longest names, and as a side answer: the number of rows that contain
>>the same ID. Thank you, Steve
>>
>>This gets me into the ballpark and I suspect that foxpro can do this:
>>
>>SELECT r1.id, r1.name, LEN(ALLTRIM(r1.name)) as l, COUNT(r1.id) ;
>>FROM aan as r1 LEFT JOIN aan as r2 on r1.id = r2.id .and.;
>>LEN(ALLTRIM(r2.name)) < LEN(ALLTRIM(r1.name)) .or.;
>>LEN(ALLTRIM(r1.name)) > LEN(ALLTRIM(r2.name));
>>GROUP BY r1.name, r1.id
>>
>>But I can't figure out what count(ID) is doing, how to return the number of rows per ID, or how to limit my results
>>to the rows that have the longest names?
>
>Based on your input, what is the desired output? The longest name here is with ID 5678 and there are 2 rows with that Id. Is it the result you want?

If possible, the output that I want would be:

ID-------Name----------------Count---Row_Count
1234----Fred--------------------1-------1
2345----Bertha Smith---------2-------2
5678----Robin Jackson-------2-------2

Where, count shows how many names there are for a given ID, row_count shows the row in which the longest name appears, and name shows the longest name for each ID.

Thank you, Steve