6 Replies Latest reply: Feb 22, 2014 3:30 PM by Srikanth P RSS

    2nd Value Group By

    Byron Van Wyk

      Hi All,

       

      I am battling to figure this out. Below is my sample data

       

      Data:

      LOAD * INLINE [

      ID, HistoryID, TimeStamp

      1,1,02/08/2014 14:00:00

      1,2,02/08/2014 14:26:00

      1,3,02/08/2014 14:56:00

      2,4,04/08/2014 11:05:00

      2,5,04/08/2014 12:15:00

      3,6,06/08/2014 13:00:00

      3,7,06/08/2014 13:50:00

      3,8,07/08/2014 14:00:00

      3,9,07/08/2014 14:26:00

       

      What I want is a table that has the 2nd value for each grouped ID added as a new column

       

      i.e.

       

      ID, HistoryID, TimeStamp, 2ndValue

      1,1,02/08/2014 14:00:00,02/08/2014 14:26:00

      1,2,02/08/2014 14:26:00,02/08/2014 14:26:00

      1,3,02/08/2014 14:56:00,02/08/2014 14:26:00

      2,4,04/08/2014 11:05:00,04/08/2014 12:15:00

      2,5,04/08/2014 12:15:00,04/08/2014 12:15:00

      3,6,06/08/2014 13:00:00,06/08/2014 13:50:00

      3,7,06/08/2014 13:50:00,06/08/2014 13:50:00

      3,8,07/08/2014 14:00:00,06/08/2014 13:50:00

      3,9,07/08/2014 14:26:00,06/08/2014 13:50:00

       

      How do I do this in a simple way.

       

      Please urgently need your help figuring this one out

       

       

      Cheers,

      Byron