Help with Max() and Min()

[eduardo_dimperio]

Hi everyone, Happy new year (delayed)

Im having some complication using max() and min(). I understand the reason of my problem, but i need some help to find an answer,

Well, I need to get the consume of energy in a month, its quite simple just pick the first measure and the last measure of a meter and do one less the other.

The problem are, max() and min() aparently works analising the intire row, for that reason i get more then one measure for meter.

How can i solve that?

i.e

meter = 36075

i just want from output VAL_MAX = 120062 and DATA_MAX=09/12/16 04 00 05, but i get more then that.

Follow the code:

CONSUMO_MAX:

LOAD

    OID_METER,

    MAX(DATE_READ)  AS DATA_MAX,

    VALUE_READ AS VAL_MAX

    RESIDENT TMP2

    GROUP BY OID_METER,VALUE_READ

    ORDER BY OID_METER,VALUE_READ DESC;

[sunny_talwar]

I am not sure what you intend order by to do here, but as long as you group by DATE_READ, you will have grouping by multiple DATE_READs which essentially means not grouping at all in your case.

[eduardo_dimperio]

Wow Sunny, it works i get the same result using:

CONSUMO_MAX:

LOAD

    OID_METER,

    Max(DATE_READ) as DATA_MAX,

    FirstSortedValue(VALUE_READ, -DATE_READ) as VAL_MAX

Resident TMP2

GROUP BY OID_METER;

OR

CONSUMO_MAX:

LOAD

    OID_METER,

    MAX(DATE_READ)  AS DATA_MAX,

    FirstSortedValue(VALUE_READ, -DATE_READ) as VAL_MAX

Resident TMP2

Group By OID_METER;

Now i just need understant the concept of using group by date_read and not using, to full understand this code.

Thank you !!!