I don’t think your query is valid. Do you perhaps need PARTITION BY? SELECT LAST_VALUE(FIELD) over (ORDER BY FIELD2 PARTITION BY FIELD3) FROM TABLE
> On Mar 11, 2019, at 1:18 PM, Pablo Sepulveda <[email protected]> wrote: > > 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>
