James David Smith <james.david.smith@gmail.com> writes:
> ... 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.
You could do it with SELECT DISTINCT ON; see the "weather reports"
example in the SELECT reference page in the PG manual.
The more SQL-standard way is to use a subselect, viz
SELECT whatever
FROM camdengps3 upper
WHERE date_time = (SELECT MIN(date_time) FROM camdengps3 lower
WHERE lower.crimes_link = upper.crimes_link);
However this is generally a lot slower, and it also outputs
multiple rows if there are multiple rows meeting the MIN date_time
in any particular group, which might not be what you want.
regards, tom lane
