3 Replies Latest reply: Jul 27, 2015 1:02 PM by durga seelam

# Help me to find the lowest date?

Hi All,

 DATE 12/29/14 1:52:03 PM 1/5/15 12:12:33 PM 1/5/15 12:10:33 PM 1/5/15 12:05:33 PM 1/5/15 12:25:33 AM 1/5/15 12:25:33 PM 1/9/15 11:37:28 AM 1/9/15 11:37:28 AM 1/9/15 11:42:28 AM 1/15/15 12:25:33 PM 8/9/15 11:37:28 AM 8/9/15 11:37:28 AM 10/5/15 12:25:33 PM 10/5/15 12:22:33 PM 10/9/15 9:37:28 AM 10/9/15 10:37:28 AM 10/9/15 11:37:28 AM 10/15/15 12:25:33 PM 10/19/15 11:43:28 AM 11/5/15 12:25:33 PM 11/11/15 11:51:28 AM 11/19/15 12:15:33 PM 11/25/15 12:25:33 PM

Now my scenario is to find the date having the minimum timestamp for respective dates. My output to be as follow.

 DATE 12/29/14 1:52:03 PM 1/5/15 12:25:33 AM 1/9/15 11:37:28 AM 1/15/15 12:25:33 PM 8/9/15 11:37:28 AM 10/5/15 12:22:33 PM 10/9/15 9:37:28 AM 10/15/15 12:25:33 PM 10/19/15 11:43:28 AM 11/5/15 12:25:33 PM 11/11/15 11:51:28 AM 11/19/15 12:15:33 PM 11/25/15 12:25:33 PM

Thanks,

Durga

• ###### Re: Help me to find the lowest date?

SET TimestampFormat = 'M/D/YY h:mm:ss TT';

LOAD Timestamp(min(DATE)) as DATE INLINE [

DATE

12/29/14 1:52:03 PM

1/5/15 12:12:33 PM

1/5/15 12:10:33 PM

1/5/15 12:05:33 PM

1/5/15 12:25:33 AM

1/5/15 12:25:33 PM

1/9/15 11:37:28 AM

1/9/15 11:37:28 AM

1/9/15 11:42:28 AM

1/15/15 12:25:33 PM

8/9/15 11:37:28 AM

8/9/15 11:37:28 AM

10/5/15 12:25:33 PM

10/5/15 12:22:33 PM

10/9/15 9:37:28 AM

10/9/15 10:37:28 AM

10/9/15 11:37:28 AM

10/15/15 12:25:33 PM

10/19/15 11:43:28 AM

11/5/15 12:25:33 PM

11/11/15 11:51:28 AM

11/19/15 12:15:33 PM

11/25/15 12:25:33 PM

]

GROUP BY floor(DATE);

• ###### Re: Help me to find the lowest date?

Try this script:

Table:

Date(Floor(DATE)) as Date,

Time(Frac(DATE)) as Time;

DATE

12/29/14 1:52:03 PM

1/5/15 12:12:33 PM

1/5/15 12:10:33 PM

1/5/15 12:05:33 PM

1/5/15 12:25:33 AM

1/5/15 12:25:33 PM

1/9/15 11:37:28 AM

1/9/15 11:37:28 AM

1/9/15 11:42:28 AM

1/15/15 12:25:33 PM

8/9/15 11:37:28 AM

8/9/15 11:37:28 AM

10/5/15 12:25:33 PM

10/5/15 12:22:33 PM

10/9/15 9:37:28 AM

10/9/15 10:37:28 AM

10/9/15 11:37:28 AM

10/15/15 12:25:33 PM

10/19/15 11:43:28 AM

11/5/15 12:25:33 PM

11/11/15 11:51:28 AM

11/19/15 12:15:33 PM

11/25/15 12:25:33 PM

];

Right Join(Table)

Min(Time) as Time

Resident Table

Group By Date;

Output:

• ###### Re: Help me to find the lowest date?

Sunny and Sweuhl, both are working. Thanks for response.