You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Pablo Sepulveda <ps...@entix.cl> on 2019/03/11 20:18:40 UTC
LAST_VALUE FUNCTION
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>
Re: LAST_VALUE FUNCTION
Posted by Julian Hyde <jh...@apache.org>.
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 <ps...@entix.cl> 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>