Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
is there a way to load only row with max value in table having dupicate records for same values for a particular ID column. i want this to be done in load script
ID, Col1,Col2,
1,aa,17
1,bb,34
1,aa,12
2,hh,14
2,hh,23
i want the output as
ID,Col1,Col2
1,bb,34
2,hh,23
Data:
LOAD * INLINE [
ID, Col1,Col2,
1,aa,17
1,bb,34
1,aa,12
2,hh,14
2,hh,23
];
Result:
LOAD ID,
FirstSortedValue(Col2, -Col2) as MaxCol2,
FirstSortedValue(Col1, -Col2) as MaxCol1
RESIDENT Data
GROUP BY ID;
DROP Table Data;
Try
Data:
LOAD * INLINE [
ID, Col1,Col2,
1,aa,17
1,aa,34
1,aa,12
2,hh,14
2,hh,23
];
LOAD ID,
Max(Col2) as MaxCol,
Col1
RESIDENT Data
GROUP BY ID, Col1;
DROP Table Data;
OR
Data:
LOAD * INLINE [
ID, Col1,Col2,
1,aa,17
1,aa,34
1,aa,12
2,hh,14
2,hh,23
];
LOAD ID,
FirstSortedValue(Col2, -Col2) as MaxCol,
Col1
RESIDENT Data
GROUP BY ID, Col1;
DROP Table Data;
Thanks i just realised that the values of col1 may vary hence updated the data in discussion and this is sample i am having few more columns in my actual data
Data:
LOAD * INLINE [
ID, Col1,Col2,
1,aa,17
1,bb,34
1,aa,12
2,hh,14
2,hh,23
];
Result:
LOAD ID,
FirstSortedValue(Col2, -Col2) as MaxCol2,
FirstSortedValue(Col1, -Col2) as MaxCol1
RESIDENT Data
GROUP BY ID;
DROP Table Data;