Or you could try a stored proc. For example (partial code):

for select max(id) from user_location group by username into :current_id
do
begin
select id, username, latitude, longitude from user_location where id =
:current_id
into :id, :username, :latitude, :longitude;
suspend;
end

However, it looks to me like you're trying to implement a master detail
relationship but without the master! How about using 2 tables instead:

user_location_current (username, latitude, longitude)
user_location_history (same as your existing user_location table)

Add an UPDATE trigger to user_location_current that inserts a new record
into user_location_history.
The latest position of every user is then instantly accessible in
user_location_current.

OK, there is some duplication in data, but I'm sure disk space is
cheaper than your users time :-)

HTH
Regards
Richard Salt

-----Original Message-----
From: Tony Landes
Sent: 06 October 2005 16:28
To: 'firebird-support@yahoogroups.com'
Subject: [firebird-support] Subquery with group by


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]





------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links