1 Reply Latest reply: Aug 2, 2012 7:59 AM by Goran Korsgren RSS

    filtering on highest x for each y

    Goran Korsgren

      Hi!

       

      Example data is like this:

       

      t1:

      LOAD * INLINE [

          person, rowid, phone_no

          Joe, 1, 1234567

          Joe, 2, 7880833

          Jack, 3, 8797400

          Jack, 4, 5866969

      ];

       

       

      What's the smartest way to create a new table t2 from t1 with only person and one phone_no per person, where the phone_no used is the one with highest rowid for each person:

       

      Resulting table:

      t2:

      person, phone_no

      Joe, 7880833

      Jack, 5866969