pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedvalue from max row in group by
6+ messages / 4 participants
[nested] [flat]
* value from max row in group by
@ 2013-07-25 17:45 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 6+ messages in thread
From: Gary Stainburn @ 2013-07-25 17:45 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value from max row in group by
@ 2013-07-25 17:57 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 6+ messages in thread
From: Gary Stainburn @ 2013-07-25 17:57 UTC (permalink / raw)
To: pgsql-sql
As usual, once I've asked the question, I find the answer myself.
However, it *feels* like there should be a more efficient way. Can anyone
comment or suggest a better method?
timetable=> select stts_id, stts_offset+stts_duration as total_duration
timetable-> from standard_trip_sections
timetable-> where (stts_id, stts_offset) in
timetable-> (select stts_id, max(stts_offset) from standard_trip_sections
group by stts_id);
stts_id | total_duration
---------+----------------
1 | 01:35:00
2 | 01:35:00
3 | 01:08:00
4 | 01:38:00
5 | 01:03:00
6 | 01:06:00
(6 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value from max row in group by
@ 2013-07-25 17:58 bricklen <bricklen@gmail.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 6+ messages in thread
From: bricklen @ 2013-07-25 17:58 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
On Thu, Jul 25, 2013 at 10:45 AM, Gary Stainburn <
gary.stainburn@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;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value from max row in group by
@ 2013-07-25 18:03 Venky Kandaswamy <venky@adchemy.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Venky Kandaswamy @ 2013-07-25 18:03 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
You can use Postgres WINDOW functions for this in several different ways. For example, one way of doing it:
select stts_id, last_value(stts_offset) over (partition by stts_id order by stts_offset desc)
+ last_value(stts_duration) over (partition by stts_id order by stts_offset desc)
from table
group by stts_id;
________________________________________
Venky Kandaswamy
Principal Engineer, Adchemy Inc.
925-200-7124
________________________________________
From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Gary Stainburn <gary.stainburn@ringways.co.uk>
Sent: Thursday, July 25, 2013 10:57 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] value from max row in group by
As usual, once I've asked the question, I find the answer myself.
However, it *feels* like there should be a more efficient way. Can anyone
comment or suggest a better method?
timetable=> select stts_id, stts_offset+stts_duration as total_duration
timetable-> from standard_trip_sections
timetable-> where (stts_id, stts_offset) in
timetable-> (select stts_id, max(stts_offset) from standard_trip_sections
group by stts_id);
stts_id | total_duration
---------+----------------
1 | 01:35:00
2 | 01:35:00
3 | 01:08:00
4 | 01:38:00
5 | 01:03:00
6 | 01:06:00
(6 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value from max row in group by
@ 2013-07-25 18:53 Marc Mamin <M.Mamin@intershop.de>
parent: Venky Kandaswamy <venky@adchemy.com>
0 siblings, 1 reply; 6+ messages in thread
From: Marc Mamin @ 2013-07-25 18:53 UTC (permalink / raw)
To: Venky Kandaswamy <venky@adchemy.com>; Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
>________________________________________
>Von: pgsql-sql-owner@postgresql.org [pgsql-sql-owner@postgresql.org]" im Auftrag von "Venky >Kandaswamy [venky@adchemy.com]
>
>You can use Postgres WINDOW functions for this in several different ways. For example, one way of doing it:
>
>select stts_id, last_value(stts_offset) over (partition by stts_id order by stts_offset desc)
> + last_value(stts_duration) over (partition by stts_id order by stts_offset desc)
>from table
>group by stts_id;
another simple solution with distinct on:
select distinct on (stts_id, stts_offset) stts_id, stts_offset+stts_duration
from table
order by stts_id, stts_offset desc
Marc Mamin
________________________________________
From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Gary Stainburn <gary.stainburn@ringways.co.uk>
Sent: Thursday, July 25, 2013 10:57 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] value from max row in group by
As usual, once I've asked the question, I find the answer myself.
However, it *feels* like there should be a more efficient way. Can anyone
comment or suggest a better method?
timetable=> select stts_id, stts_offset+stts_duration as total_duration
timetable-> from standard_trip_sections
timetable-> where (stts_id, stts_offset) in
timetable-> (select stts_id, max(stts_offset) from standard_trip_sections
group by stts_id);
stts_id | total_duration
---------+----------------
1 | 01:35:00
2 | 01:35:00
3 | 01:08:00
4 | 01:38:00
5 | 01:03:00
6 | 01:06:00
(6 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value from max row in group by
@ 2013-07-26 08:18 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Marc Mamin <M.Mamin@intershop.de>
0 siblings, 0 replies; 6+ messages in thread
From: Gary Stainburn @ 2013-07-26 08:18 UTC (permalink / raw)
To: pgsql-sql
Sorry, but I never thought.
I'm developing this on my server I'm developing it for someone else who wants
it in a WordPress / MySQL environment (I don't know MySQL).
Would this or something similar work in mysql?
(Sorry for going O.T.)
On Thursday 25 July 2013 19:53:06 Marc Mamin wrote:
> >________________________________________
> >Von: pgsql-sql-owner@postgresql.org [pgsql-sql-owner@postgresql.org]"
> > im Auftrag von "Venky >Kandaswamy [venky@adchemy.com]
> >
> >You can use Postgres WINDOW functions for this in several different ways.
> > For example, one way of doing it:
> >
> >select stts_id, last_value(stts_offset) over (partition by stts_id order
> > by stts_offset desc) + last_value(stts_duration) over (partition by
> > stts_id order by stts_offset desc) from table
> >group by stts_id;
>
> another simple solution with distinct on:
>
> select distinct on (stts_id, stts_offset) stts_id,
> stts_offset+stts_duration from table
> order by stts_id, stts_offset desc
>
> Marc Mamin
>
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-07-26 08:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-25 17:45 value from max row in group by Gary Stainburn <gary.stainburn@ringways.co.uk>
2013-07-25 17:57 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2013-07-25 18:03 ` Venky Kandaswamy <venky@adchemy.com>
2013-07-25 18:53 ` Marc Mamin <M.Mamin@intershop.de>
2013-07-26 08:18 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2013-07-25 17:58 ` bricklen <bricklen@gmail.com>
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