14 Replies Latest reply: Apr 7, 2016 7:30 AM by Gysbert Wassenaar RSS

    Complex sorting scenario - haven't yet had the time to find an easy solution ...

    Friedrich Hofmann

      Hi,

       

      I have a complex sorting scenario that i couldn't yet come up with an easy solution for - I think I know a way to do it, but a rather complex one, maybe someone here knows an easier way.

      I can try to put together some sample data, but it's rather easy on the data_side:

       

      - I have a number of different item_numbers;

      - For every item_number, there is a number of records, each with a unique date-time-stamp;

      - What I want as output is a table that has, for every item_nr, only the latest record (with the most up-to-date date-time-stamp)

       

      => So, to begin with, I would sort in a RESIDENT LOAD by

           - the item_no (asc.)

           - the date-time-stamp (desc)

      => Then I was thinking of using a >> FIRST 1 LOAD << - but that would only fetch the one first record, so I'd need that once per

            item_no

      => To accomplish that, I was thinking of using a >> LOAD DISTINCT [item_no] << to create an auxiliary table and

            to parse this table line by line and in every iteration, make a variable and then >> FIRST 1 LOAD << from the other

            table with a WHERE clause filtering for that one item_no

      ==> That way I'd get the most up-to-date record for every item_no

       

      <=> The drawback is, that is a rather complex solution. So that's my question - is there an easier way?

      Thanks a lot!

      Best regards,

       

      DataNibbler