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
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'.
start (crimes_link, date_time) AS
SELECT crimes_link, min(date_time)
GROUP BY crimes_link
l.crimes_link = s.crimes_link AND
l.date_time = s.date_time
In response to
pgsql-novice by date
|Next:||From: JORGE MALDONADO||Date: 2011-07-10 18:08:25|
|Subject: pgpass.cong file becomes empty|
|Previous:||From: James David Smith||Date: 2011-07-09 22:31:19|
|Subject: Re: Beginner Question...|