Hi All

I have the following table:

CREATE TABLE "USER_LOCATION"
(
"ID" NUMERIC(18,0) NOT NULL,
"USERNAME" VARCHAR(64) NOT NULL,
"FIXTIME" TIMESTAMP NOT NULL,
"LATITUDE" NUMERIC(9,0) NOT NULL,
"LONGITUDE" NUMERIC(9,0) NOT NULL,
PRIMARY KEY ("ID")
);

Each entry is the location of a user at a give point in time (from their GPS).

The ID field is derived from a generator and I'd like to use the following query to get the latest position of each user.

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

The subquery works fast enough on its own but the combined query is slow (~6000 entries spread over 10 users).

Am I right in assuming that I should do this in code as separate queries instead of trying to combine everything into a single query?

All suggestions gratefully received.

Regards

Terry Child


[Non-text portions of this message have been removed]