Skip site navigation (1) Skip section navigation (2)

Re: Beginner Question...

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Beginner Question...
Date: 2011-07-09 17:53:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
James David Smith <james(dot)david(dot)smith(at)gmail(dot)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

In response to

pgsql-novice by date

Next:From: James David SmithDate: 2011-07-09 22:31:19
Subject: Re: Beginner Question...
Previous:From: Michael WoodDate: 2011-07-09 17:50:13
Subject: Re: Beginner Question...

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group