You can try
I didn't test it, so it's a shot in the dark, but I think it might work. Anyone feel free to shoot it down...
WHERE VISIT >= (SELECT MAX(VISIT) FROM TEMP1 WHERE ID = TEMP2.ID);
As far as running fast, you can't do this in one query without having a correlated subquery containing an aggretate function, so the speed factor is really more of a constraint on your database engine and the system on which it runs than on the query itself. Its complexity is rather fixed.