max function

[elmasduro]

i have two tables
one is call temp1 and looks like this:

id visit
1 1
1 3
2 1
2 2


the other table, temp2, looks like this
id visit days flag
1 1 1 n
1 2 3 y
1 3 4 y
1 4 3 n
1 5 1 n
2 1 3 n
2 2 4 y
2 3 4 n

what i want to do is get the max visit for a given id (ie id 1)
and get all the visit from temp2 that are equal OR greater than the max visit of temp1

for example
for id 1 in temp1 table, the max visit is 3
so i should get
id visit days flag
1 3 4 y
1 4 3 n
1 5 1 n

from temp2 table.

i would like to do this in one single query if possible.
I WILL BE USING THIS IN ONE OF MY PROJECT SO THE QUERY WILL NEED TO RUN FAST. TEMP1 AND TEMP2 CONTAIN THOUSANDS OF ROWS. can someone tell me how to accomplish this the fastest way? any other methods of doing this is acceptable. thanks

[ks5d]

You can try

SELECT *
FROM TEMP2
WHERE VISIT >= (SELECT MAX(VISIT) FROM TEMP1 WHERE ID = TEMP2.ID);

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

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.

--Edwin