Re: Beginner Question...

On 9 July 2011 18:41, James David Smith <james.david.smith@gmail.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@gmail.com>