Just a correction to my original query

> SELECT * FROM user_location WHERE id IN (SELECT max(FixTime) FROM
> user_location group by username)

Would obviously not work unless somehow by some fluke the fixtime
happenned to equal the id, I suppose what I was trying to say was
that the order the data is inserted (I am assuming IDs are handed out
sequentially by a generator) can possibly be different to the order
the data was originally collected.

This is especially a consideration with remote sites. Say I was at
location 1 and recorded a user_location, but the communications were
down, so your program either stored it locally for the moment or the
user wrote it down on a piece of paper to add later.

I then travelled to another location, and my user_location for the
second location was able to send the data immediately. A few minutes
later (while I am still physically at the second location), the
record from the first location is finally sent because the internet
came back online for them.

So I would do it the second way I suggested, using the stored
procedure to add. Except in hindsight, I should have queried
the "current" user location before running the update query, and only
run the update query if the timestamp recorded was more recent than
the one that is currently there.

Adam

> But I would attack this differently
>
> Add a table to hold the current location for each user.
>
> CREATE TABLE USER_CURRENT_LOCATION
> (
> USER_LOCATION_ID NUMERIC(18,0) NOT NULL,
> USERNAME VARCHAR(64) NOT NULL
> );
>
> Create a stored procedure to record their current location
>
> CREATE PROCEDURE SP_SET_USER_LOCATION
> (
> USERNAME VARCHAR(64) NOT NULL,
> FIXTIME TIMESTAMP NOT NULL,
> LATITUDE NUMERIC(9,0) NOT NULL,
> LONGITUDE NUMERIC(9,0) NOT NULL
> )
> RETURNS
> (
> ID NUMERIC(18,0) NOT NULL
> )
> AS
> BEGIN
> ID = GEN_ID(GEN_USER_LOCATION_ID, 1);
>
> INSERT INTO USER_LOCATION (....) VALUES (....);
>
> UPDATE USER_CURRENT_LOCATION
> SET USER_LOCATION_ID = :ID
> WHERE USERNAME = :USERNAME

;

> END
> ^
>
> Then to get the current location, it is a simple join.
>
> Adam