Help with max value

[qvqfqlik]

hi,

please I need help with getting the record  with max value "cycle" number for the combination of  ID, name in the below sample data.

ID	name	cycle	number	place	dept	country
12	a	1	n1	place1	dept1	c1
12	a	2	n1	place2	dept2	c2
13	a	1	n2	place1	dept2	c1
13	a	2	n2	place2	dept2	c2
13	a	3	n2	place3	dept3	c4
14	a	1	n3	place4	dept1	c5

Required output

number	place	dept	country
n1	place2	dept2	c2
n2	place3	dept3	c4
n3	place4	dept1	c5

[sunny_talwar]

You need to do this in the script or a front end chart?

[qvqfqlik]

anything works Sunny.

can you please give solution for both. Thankyou

[sunny_talwar · Accepted solution]

Script based

Table:
LOAD * INLINE [
    ID, name, cycle, number, place, dept, country
    12, a, 1, n1, place1, dept1, c1
    12, a, 2, n1, place2, dept2, c2
    13, a, 1, n2, place1, dept2, c1
    13, a, 2, n2, place2, dept2, c2
    13, a, 3, n2, place3, dept3, c4
    14, a, 1, n3, place4, dept1, c5
];

Right Join (Table)
LOAD number,
	 Max(cycle) as cycle
Resident Table
Group By number;

Will remove three of the unwanted rows from the database

[qvqfqlik]

Thankyou Sunny