Re: Fwd: Tricky join and update with same table - Mailing list pgsql-novice

On Fri, Jul 26, 2013 at 5:24 AM, James David Smith
<james.david.smith@gmail.com> wrote:
> Hi all,
>
> Some help if you can please. I have GPS data for a number of people. Each
> person turned the GPS on, at home, at a different time of the day. Before
> that time, I presume that the person was in their house. I have made a table
> for each person for a whole 24 hours (one record per minute), and I now want
> to 'fill in' their location for the rows before they turned the GPS on. So
> for each person I want to take the first row where the point_geom is not
> null, and update all of the rows above it with that value. It's driving me
> nuts.
>
> spid     |     point_time                 |     point_geom
> -----------------------------------------------------------
> 1         |     2012-01-01 00:01:00   |
> 1         |     2012-01-01 00:02:00   |
> 1         |     2012-01-01 00:03:00   |     POINT(X, Y)
> 1         |     2012-01-01 00:04:00   |     POINT(X, Y)
> 1         |     2012-01-01 00:05:00   |     POINT(X, Y)
> 2         |     2012-01-01 00:01:00   |
> 2         |     2012-01-01 00:02:00   |
> 2         |     2012-01-01 00:03:00   |
> 2         |     2012-01-01 00:04:00   |
> 2         |     2012-01-01 00:05:00   |     POINT(X, Y)
> 3         |     2012-01-01 00:01:00   |
> 3         |     2012-01-01 00:02:00   |     POINT(X, Y)
> 3         |     2012-01-01 00:03:00   |     POINT(X, Y)
> 3         |     2012-01-01 00:04:00   |     POINT(X, Y)
> 3         |     2012-01-01 00:05:00   |     POINT(X, Y)
>
> I've managed to select the correct row using this:
>
> SELECT spid, min(point_time) as point_time
> FROM hlhs_day
> WHERE point_geom IS NOT NULL
> GROUP BY spid;
>
> However when I try to add in the column 'point_geom' to the query, it won't
> work.
>
> Thanks for your help and suggestions.
>
> James

Usually when I chime in on questions like this, someone comes along
with a better solution after I finish.  But I think you can get the
information you want by "wrapping" that query:

SELECT hd1.spid, hd1.point_time, hd2.point_geom
FROM
(SELECT spid, min(point_time) as point_time
FROM hlhs_day
WHERE point_geom IS NOT NULL GROUP BY spid) as hd1
INNER JOIN hlhs_day hd2 ON hd1.spid = hd2.spid AND hd1.point_time =
hd2.point_time
ORDER BY hd1.spid;

I hope this helps.
--Mike