>>>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

Are you using VFP tables or SQL Server?

Do you have unique Id in the table that is responsible for row_count?

Without Row_Count in VFP

select M1.ID, T.Name, M1.CountNames from (select Id, max(len(name)) as MaxLenghtName, count(Name) as CountNames from myTable) M1
inner join myTable.T ON M1.Id = T.Id and M1.MaxLenghtName = LEN(T.Name)

From the top of my head.

In SQL Server it's simpler:

;with cte as (select *, row_number() over (partition by Id order by len(name) DESC) as Rn, count(*) over (partition by Id) as CountNames from myTable)

select * from cte where Rn = 1