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