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>