Skip site navigation (1) Skip section navigation (2)

Re: Beginner Question...

From: Michael Wood <esiotrot(at)gmail(dot)com>
To: James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Beginner Question...
Date: 2011-07-09 17:50:13
Message-ID: CAP6d-HUCS8DBsUbqpRuVK1xcZExJJez69zkVwGJToLASefngMw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 9 July 2011 18:41, James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
> Hi everyone,
>
> Could someone help me with this question please? I have a table with
> four columns:
>
> - gps_id (primary key)
> - date_time
> - crimes_link (foreign key)
> - osgb36_geom
>
> The data in the table involves lots of gps tracks of different
> journeys. They are grouped into journeys by the 'crimes_link' field.
> So for example the first 50 rows have an identical 'crimes_link'
> field, then the next 50 rows a different value in 'crimes_link', then
> the next 50 rows a different one, etc. What I would like to do is to
> select the beginning location of each journey. This query gives me the
> date_time of the beginning of the journey:
>
> SELECT crimes_link, MIN(date_time)
> FROM camdengps3
> GROUP BY crimes_link;
>
> However I need to add the osgb36_geom column into the query and am unable too.
>
> Any ideas how to do this please?

Sorry, I don't have time to test this now and maybe someone else has a
better way, but maybe it will give you and idea:

SELECT l.date_time, l.crimes_link, l.osgb36_geom
FROM camdengps3 AS l INNER JOIN (
    SELECT crimes_link, MIN(date_time) AS start
    FROM camdengps3
    GROUP BY crimes_link) AS r
        ON l.date_time = s.start AND l.crimes_link = s.crimes_link;

-- 
Michael Wood <esiotrot(at)gmail(dot)com>

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2011-07-09 17:53:48
Subject: Re: Beginner Question...
Previous:From: Daniel StaalDate: 2011-07-09 17:12:28
Subject: Re: Best Practice for Address storage.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group