--- In firebird-support@yahoogroups.com, "Terry Child" wrote:

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

Change this to:

SELECT * FROM user_location u1 WHERE NOT EXISTS(
SELECT * FROM user_location u2
where u2.username = u1.username and u2.id > u1.id)

That your original select takes long to execute indicates that it
executes the subselect for every row of the table (I think Arno did
something to replace such subselects with 'NOT EXISTS' wherever
possible, but maybe your GROUP BY beats the optimizer - anyway, I
think using IN <subselect> ought to give a "query too poor for
optimizer error" ;o), and I have yet to see a case where such a query
cannot be replaced by EXISTS and/or NOT EXISTS).

Set