group by FirstSortedValue

[entsh]

hi

in edit script i used this code

select

code,name,number from....

the result is:

code	name	number
5	teh	500
6	teh	600
7	teh	300
4	ker	100
5	ker	200
6	ker	400
8	un	500

but i want this

for each name just show the maximum code:(code for edit script)

code	name	number
7	teh	300
6	ker	400
8	un	500

would u please help me?

[Taoufiq_Zarra · Accepted solution]

@entsh  Maye be :

Data:
LOAD * INLINE [
    code, name, number
    5, teh, 500
    6, teh, 600
    7, teh, 300
    4, ker, 100
    5, ker, 200
    6, ker, 400
    8, un, 500
];

output:
noconcatenate

load Max(code) as code,name,FirstSortedValue(number,-code) as number resident Data group by name;

drop table Data;

output:

[AshutoshBhumkar · Accepted solution]

Hello,

Another way is to create flag. Though the way @Taoufiq_Zarra has shown is absolutely correct. 🙂

Table:
Load code, name,number,code&'-'&name as codeName;
Load * Inline [
code,name,number
5,teh,500
6,teh,600
7,teh,300
4,ker,100
5,ker,200
6,ker,400
8,un,500
];

NoConcatenate
Table2:
Load
Max(code)&'-'&name as codeName,
'1' as Flag
resident Table group by name;

Thanks,

Ashutosh

[MarcoWedel · Accepted solution]

Hi,

another solution might be to purge unnecessary lines with a right join like this:

If your data is loaded in table1, then

Right Join (table1)
LOAD name,
     Max(code) as code
Resident table1
Group By name;

hope this helps
regards
Marco