Re: Beginner Question...

On 10/07/11 04:41, James David Smith wrote:
> - 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?
>
> Thank you
>
> James Smith
Hi ,

I would recommend adding an index, as follows:

CREATE INDEX ON location(crimes_link, date_time);

I tested the followiunmg query, I and observed that the above index
improves performance. I used pg 9.1beta2, but this should also work for
any pg veresion that imoplements the 'WITH' clause on 'SELECT'.

WITH
start (crimes_link, date_time) AS
(
SELECT crimes_link, min(date_time)
FROM location
GROUP BY crimes_link
)
SELECT
l.gps_id,
l.date_time,
l.crimes_link,
l.osgb36_geom
FROM
location l,
start s
WHERE
l.crimes_link = s.crimes_link AND
l.date_time = s.date_time
ORDER BY
l.gps_id;

Cheers,
Gavin