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