Get earliest timestamp of the day for each day

[#1]

Hi everyone,

Looked through some blogs but didn't find what I was looking for.

Here's a sample of my data.

ID          StartTime                       EndTime

100        10/15/2012 10:00          10/15/2012 10.10

100        10/15/2012 10:00          10/15/2012 12:45

I would like to only retrieve the row of data where the EndTime date is the earliest.  In this case, it will be the first row.  I need apply this logic through for each day to grab the earliest EndTime for each day. 

Thanks!

Mark

[CELAMBARASAN]

May be use group by like

Load

     StartTime,

     TimeStamp(Min(EndTime)) AS MinEndTime

Resident

TableName

Group by StartTime;

[#3]

For more clarfication - I need to do this in the Edit Script.

Thanks.

Mark

[MayilVahanan]

Hi

Try like this

Tab1:

Load ID,StartTime,EndTime from tableName;

Inner join(Tab1)

Load ID, Min(EndTime) as EndTime resident Tab1 group by ID;

Hope it helps

[#5]

Mayil, the example worked great.  However, I just discovered that I can actually have same EndTimeStamp mulitple times for each ID.  How would I just grab the first one based on the earlier StartTimeStamp?

Thanks again.

Mark 

[MayilVahanan]

HI

Celambarasan Adhimulam  example work fine then..

Load

     StartTime,

     Min(EndTime) AS MinEndTime

Resident

TableName

Group by StartTime;

Did you worked it?

[#7 · Accepted solution]

I think I answered my own second question,

Inner join(Tab1)

Load ID, Min(StartTime) as StartTime, Min(EndTime) as EndTime resident Tab1 group by ID;