3 Replies Latest reply on Oct 22, 2012 11:26 AM by Gunnar Tapper

    Getting the latest row in a time series

    Gunnar Tapper

      Hi,

       

      I have a table laid out as follows:

       

      TimestampJob ID
      Status
      10/19/2012 5:15:29 AMjob_201210161535_0302Running
      10/19/2012 5:15:59 AMjob_201210161535_0302Completed
      10/19/2012 5:16:29 AMjob_201210161535_0303Completed
      10/19/2012 4:37:38 PMjob_201210161535_0304Running
      10/19/2012 4:38:08 PMjob_201210161535_0304Running
      10/19/2012 4:38:38 PMjob_201210161535_0304Running

       

      The columns are as follows:

       

      • Timestamp: The time the data was generated.
      • Job ID: The job identifier.
      • Status: The current status of the job (preparing, running, killed, completed, etc.)

       

      I'm trying to visualize a continuous time range where I sort jobs depending on status and then chart duration as a Gantt chart. I know how to do the visualization part but I can't figure out how to filter out all but the latest row per job automatically. The result I am after is:

       

      TimestampJob ID
      Status
      10/19/2012 5:15:59 AMjob_201210161535_0302Completed
      10/19/2012 5:16:29 AMjob_201210161535_0303Completed
      10/19/2012 4:38:38 PMjob_201210161535_0304Running

       

      The data will be refreshed every 15 minutes using partial extracts.

       

      Thanks in advance,

       

      Gunnar