On Thu, Jul 25, 2013 at 10:45 AM, Gary Stainburn < gary.stainb...@ringways.co.uk> wrote:
> Hi folks, > > I need help please. > > I have a table of trip section details which includes a trip ID, start > time as > an offset, and a duration for that section. > > I need to extract the full trip duration by adding the highest offset to > it's > duration. I can't simply use sum() on the duation as that would not include > standing time. > > Using the data below I would like to get: > > 1 | 01:35:00 > 2 | 01:35:00 > 3 | 01:06:00 > 4 | 01:38:00 > 5 | 01:03:00 > 6 | 01:06:00 > How about using a WINDOW function? Eg. select stts_id, total from (select stts_id, stts_offset+stts_duration as total, row_number() over (partition by stts_id order by stts_offset desc) as rank from sts) s where rank = 1 order by stts_id;
