Hi Terry,

Try this: (I tested with my tables)

create view current_position(id)
as
SELECT max(id) FROM user_location group by username


SELECT ul.* FROM current_position cp
join user_location ul on (ul.id=cp.id)

You should create an compound index on (username,id).

Regards.
Ali


--- In firebird-support@yahoogroups.com, "Terry Child"
<tmchild@g...> wrote:

>
> 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]