same id with different names - get longer name

[rafael5958]

My situation, I have:

id     name
1      jack S
1      jack Soon
1      jack Son
2      Ray J
2      Ray James Charles
2      Ray J C
3      Mark

I need to select only one ID with the longer name, or at lesat only one name. Result something like:

id     name
1      jack Soon
2      Ray James Charles
3      Mark

in SQL I would do something like:

select id,       
max(name) keep (dense_rank first order by length(name) desc)
from t
group by id;

But the table is already loaded in qliview from the database, how would I create a new table with that statement?

[jmartineze]

Hello Rafael,

Try something like this

A:

Load id, name, length(name) as N 

from "Table";

noconcatenate

B:

load id, max(N)as N  resident  A group by id;

inner join load * resident A;

drop table A;

J.

[rafael5958]

Didnt work.

It retrives

id	N
1	9
1
2	17
2
3	4

There was not length funcion, only len.

sample:

LOAD * INLINE [

id, name    

1,   jack S,
1,   jack Soon,
1,   jack Son,
2,   Ray J,
2,   Ray James Charles,
2,   Ray J C,
3,   Mark ];



A:

Load id,
 name, 
Len(name) as N
resident "sample";
noconcatenate

B:

load id, max(N)as N  resident  A group by id;
inner join load * resident A;
drop table A;

[jmartineze · Accepted solution]

Hello, change the last line by 

drop tables A, sample;

[sunny_talwar]

You can also try this

sample:
LOAD *,
	 Len(name) as Name_Len;
LOAD * INLINE [
    id, name
    1, jack S
    1, jack Soon
    1, jack Son
    2, Ray J
    2, Ray James Charles
    2, Ray J C
    3, Mark
];

Right Join (sample)
LOAD id,
	 Max(Name_Len) as Name_Len
Resident  sample
Group By id;

DROP Field Name_Len;

[rafael5958]

thank you all, it worked.