From: Venky Kandaswamy <venky@adchemy.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>, "pgsql-sql@postgresql.org"
 <pgsql-sql@postgresql.org>
Subject: Re: value from max row in group by
Thread-Topic: [SQL] value from max row in group by
Thread-Index: AQHOiV799cXiTB+z1EWv0UVVsteoTZl1rgYAgAABgvc=
Date: Thu, 25 Jul 2013 18:03:58 +0000
Message-ID: 
 <9d70bbaa0ce84bab8c0467c83fbc01c7@BY2PR05MB237.namprd05.prod.outlook.com>
References: <201307251845.51079.gary.stainburn@ringways.co.uk>,
 <201307251857.10326.gary.stainburn@ringways.co.uk>
In-Reply-To: <201307251857.10326.gary.stainburn@ringways.co.uk>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

You can use Postgres WINDOW functions for this in several different ways. F=
or example, one way of doing it:

select stts_id,   last_value(stts_offset) over (partition by stts_id order =
by stts_offset desc)=20
                   + last_value(stts_duration) over (partition by stts_id o=
rder 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 be=
half 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=3D> 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=3D>


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql