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

>
>
>I am not sure why did you change what I suggested:
>
>

>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 normal
>
>select M1.mID, T.mName, LEN(ALLTRIM(t.mName)) as lenName, M1.CountNames ;
>from ;
>(;
>select mId, max(len(ALLTRIM(mName))) as MaxLengthName, count(mName) as CountNames from mTable group BY mID;
>) as M1;
>inner join mTable as T ;
>ON M1.mID = T.mID and M1.MaxLengthName = LEN(ALLTRIM(T.mName)) ;
>order BY t.mID, lenName, MaxLengthName
>
>BROWSE normal
>

>
>Don't group by name and use =, not less than to get just people with longest names.

You had some small errors, at least it seemed? ID and NAME are reserved words, so I changed them to mID and mName. I assumed that your "inner join mTable.T" should be "mTable as T"? Is this correct? Also since your sub-query uses an aggregate: count, it needs a "group by" which you did not include, above. The results below, with = and without a final group by, still do not hone into the longest names. Rather, each ID along with each name is presented. Thank you, Steve

select M1.mID, T.mName, 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 MaxLenghtName = LEN(ALLTRIM(T.mName)) into TABLE TT