6 Replies Latest reply: Aug 9, 2016 4:33 AM by Edwin Kuijper RSS

    right join problem

    Edwin Kuijper

      Hello all,

      I have a problem with a selection in my script.

      My original table looks like this:

      ID1ID2VALUE
      1104
      1107
      218
      33367
      3335

       

       

      I would like to find the lowest value for every ID2 and also show the corresponding ID1, so I made the following script:

      TABLE1:
      load * INLINE [
      ID1, ID2, VALUE,
      1, 10, 4
      1, 10, 7
      2, 18,
      3, 33, 67
      3, 33, 5
      ];

       

      RIGHT JOIN(TABLE1)
      LOAD
      ID2,
      MIN(VALUE) AS VALUE
      RESIDENT TABLE1 GROUP BY ID2;

       

      The result is:

       

       

       

       

      This is not as desired, ID1 is missing in the line where there is no value.

      Can someone please tell me why ID1 is missing (I expected it to be 2) and how I can solve this?

       

      thanks,

      Edwin