Hello good afternoon,
My name is Pablo and I'm using Apache Calcite in Apache NiFi to recover the
last value with in a group, using the LAST_VALUE function.
SELECT LAST_VALUE(FIELD) over (ORDER BY FIELD2) FROM TABLE GROUP BY FIELD3
Example of rows from a NiFi FlowFile.
[ {
"FIELD3" : 4,
"FIELD" : 2,
"FIELD2" : 28777270
}, {
"FIELD3" : 4,
"FIELD" : 5,
"FIELD2" : 28777271
}, {
"FIELD3" : 4,
"FIELD" : 3,
"FIELD2" : 28777272
}, {
"FIELD3" : 4,
"FIELD" : 4,
"FIELD2" : 28777273
} ]
The expected result is to recover the value of "FIELD" asociated with the
max value of "FIELD2" that is 28777273.
{
"FIELD" : 4
}
The result I get is "Error while preparing statement". As soon as I take "GROUP
BY FIELD3" out of the query, it shows no error, but it only returns the
same value of FIELD.
[ {
"FIELD" : 2,
}, {
"FIELD" : 5,
}, {
"FIELD" : 3,
}, {
"FIELD" : 4,
} ]
Is there a correct way to do this?
Thank you very much in advance.
Sorry if it's not easy to understand, English is not my first language.
--
Best regards,
*Pablo Sepúlveda Robinson*
Consultor TI
Teléfono Celular : +56 9 61023746 <+56%209%206102%203746>
