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

I am using FOXPRO. The query runs but without results? Can you take a look and see what I am missing? It does work. However, all of the names for each ID are presented, although the longest name does have the largest lenName. Also, the query truncates all names to a max of 12 characters, for some reason?

I am using foxpro because it has both "regular" foxpro (making menus, stating the order in which things happen) scripting as well as table SQL scripting. Of course, this problem is easy to solve in contemporary databases. Should I switch to a different language/approach?
Thanks, Steve

SET SAFETY OFF
CREATE TABLE mTable (mId c(7), mName c(50))

INSERT INTO mTable (mID, mName) VALUES('1234','Jackie')
INSERT INTO mTable (mID, mName) VALUES('1234','Jackie Smith')
INSERT INTO mTable (mID, mName) VALUES('1234','Jackie S')
INSERT INTO mTable (mID, mName) VALUES('2345','Jackie Robertson')
INSERT INTO mTable (mID, mName) VALUES('2345','Jackie R')
INSERT INTO mTable (mID, mName) VALUES('4567','Jackie Fiss')
INSERT INTO mTable (mID, mName) VALUES('4567','Jackie Fission')
INSERT INTO mTable (mID, mName) VALUES('6789','Jackie')
INSERT INTO mTable (mID, mName) VALUES('6789','Jackie Smoke')
INSERT INTO mTable (mID, mName) VALUES('6789','Jackie Smoker')

BROWSE WIDTH 15

select M1.mID, T.mName, LEN(ALLTRIM(t.mName)) as lenName, M1.CountNames ;
from ;
(;
select mId, max(len(ALLTRIM(mName))) as MaxLenghtName, count(mName) as CountNames from mTable group BY mID, mName;
) as M1;
inner join mTable as T ;
ON M1.mID = T.mID and M1.MaxLenghtName > LEN(ALLTRIM(T.mName)) ;
order BY t.mID, lenName, MaxLenghtName

BROWSE WIDTH 15