Postgres Pro
Downloads
Home   >   mailing lists

Thread: value from max row in group by

value from max row in group by

From
Gary Stainburn
Date:
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=>

Re: value from max row in group by

From
Gary Stainburn
Date:
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=>

Re: value from max row in group by

From
bricklen
Date:

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;

Re: value from max row in group by

From
Venky Kandaswamy
Date:
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

Re: value from max row in group by

From
Marc Mamin
Date:
>________________________________________
>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

Re: value from max row in group by

From
Gary Stainburn
Date:
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