11 Replies Latest reply: Jan 5, 2017 8:49 AM by Eduardo DImperio RSS

    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

       

      max.JPG

       

       

       

      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;

        • Re: Help with Max() and Min()
          Sunny Talwar

          May be you need this:

           

          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;

            • Re: Help with Max() and Min()
              Eduardo DImperio

              Hey Sunny,

               

              I found a way to solve this problem and yes your code works fine too!

               

              Thank you so much

               

              by the way follow the code that i wrote to solve, just for curiosity

               

              CONSUMO:

              LOAD

                  OID_METER,

                  MAX(DATE_READ)  AS DATA_MAX

                  RESIDENT TMP2

                  GROUP BY OID_METER

                  ORDER BY OID_METER DESC;

               

              inner join(CONSUMO)

               

              LOAD

                  OID_METER,

                  DATE_READ AS DATA_MAX,

                  VALUE_READ AS VAL_MAX

                  RESIDENT TMP2

                  ORDER BY OID_METER,VALUE_READ DESC;

               

              I think your code is better, cause select just once

              • Re: Help with Max() and Min()
                Eduardo DImperio

                Sunny,

                 

                I read a litle about FirstSortedValue() to understand better, then i chance a lithe the code.


                CONSUMO_MAX:

                LOAD

                    OID_METER,

                    DATE_READ,

                    FirstSortedValue(VALUE_READ, -DATE_READ) as VAL_MAX

                Resident TMP2

                GROUP BY OID_METER,DATE_READ

                Order By OID_METER,DATE_READ;

                 

                but i get again multiple values, so i ask you, this code above should not get the fisrt value or max value of all dates and output me one single value?

                 

                Im a little confuse about that, can you explain to me?

                  • Re: Help with Max() and Min()
                    Sunny Talwar

                    This my friend:

                     

                    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;

                      • Re: Help with Max() and Min()
                        Eduardo DImperio

                        Yeah, but why i need to get the first value of a max(date_read) and just not first value of date_read?

                        whats the diference? In my head, says - "well, first value of a date must be the same no matter if is max(date_read) or date_read.

                         

                        This is drive me crazy

                         

                        and thank you for the pacience

                          • Re: Help with Max() and Min()
                            Sunny Talwar

                            If you group by DATE_READ, you will essentially have all the dates and FirstSortedValue() will end up showing all DATE_READ and there subsequent values. You need to group by a field which will end up having just one row for each OID_METER.

                             

                            Does it make sense?

                              • Re: Help with Max() and Min()
                                Eduardo DImperio

                                hummm, more or less.

                                 

                                If i have for example this dates and values: 11/12 -100. 12/12 -200, 13/12 -300 and 14/12 - 400, when i group by date_read i get all this dates, so if i do firstvalue() of that, it thought that will bring me just only  the value of the first date. Returning me 14/12 if i order by date_read desc or 11/12 if i order by date_read asc.

                                 

                                But this not occur.

                                 

                                Maybe i need to study the concept of group by a little more

                              • Re: Help with Max() and Min()
                                Sunny Talwar

                                You can do this, if you don't want max date

                                 

                                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;

                                 

                                But you cannot group by DATE_READ

                                  • Re: Help with Max() and Min()
                                    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 !!!