From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: value from max row in group by
Date: Thu, 25 Jul 2013 18:45:51 +0100
Message-ID: <201307251845.51079.gary.stainburn@ringways.co.uk> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
from
timetable=> select stts_id, stts_offset, stts_duration from
standard_trip_sections order by stts_id, stts_offset;
stts_id | stts_offset | stts_duration
---------+-------------+---------------
1 | 00:00:00 | 00:18:00
1 | 00:19:00 | 00:26:00
1 | 00:47:00 | 00:13:00
1 | 01:13:00 | 00:22:00
2 | 00:00:00 | 00:18:00
2 | 00:20:00 | 00:09:00
2 | 00:29:00 | 00:17:00
2 | 00:50:00 | 00:13:00
2 | 01:13:00 | 00:22:00
3 | 00:00:00 | 00:20:00
3 | 00:28:00 | 00:15:00
3 | 00:44:00 | 00:22:00
3 | 00:48:00 | 00:20:00
4 | 00:00:00 | 00:20:00
4 | 00:28:00 | 00:15:00
4 | 00:48:00 | 00:13:00
4 | 01:01:00 | 00:13:00
4 | 01:18:00 | 00:20:00
5 | 00:00:00 | 00:18:00
5 | 00:20:00 | 00:09:00
5 | 00:29:00 | 00:17:00
5 | 00:50:00 | 00:13:00
6 | 00:00:00 | 00:15:00
6 | 00:20:00 | 00:13:00
6 | 00:33:00 | 00:13:00
6 | 00:46:00 | 00:20:00
(26 rows)
timetable=>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: value from max row in group by
In-Reply-To: <201307251845.51079.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox