From: | bricklen <bricklen(at)gmail(dot)com> |
---|---|
To: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: value from max row in group by |
Date: | 2013-07-25 17:58:53 |
Message-ID: | CAGrpgQ8i=cOJ7aYHmWcp0BcvbmZN6Bv4u9+9q=Rkuhg5+aSv2w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, Jul 25, 2013 at 10:45 AM, Gary Stainburn <
gary(dot)stainburn(at)ringways(dot)co(dot)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;
From | Date | Subject | |
---|---|---|---|
Next Message | Venky Kandaswamy | 2013-07-25 18:03:58 | Re: value from max row in group by |
Previous Message | Gary Stainburn | 2013-07-25 17:57:10 | Re: value from max row in group by |