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