Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
You need to do this in the script or a front end chart?
anything works Sunny.
can you please give solution for both. Thankyou
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
Thankyou Sunny