with the order by outside the apply, all you have is an inner join and you'll return one record. What are you actually trying to accomplish with the outer apply. It looks like a simple join is all you need.Jim

hi jimf, it's not an inner join because pid is not unique in the whoHasSocks table so the top 1 order by will only return one entry for home many socks they have not the total number of socks in their possession.

hello visakh16, I was trying to avoid the nest and hoping the query optimizer would work out that it would only have to run it once, do you know if this is the case?

hi jimf, it's not an inner join because pid is not unique in the whoHasSocks table so the top 1 order by will only return one entry for home many socks they have not the total number of socks in their possession.

hello visakh16, I was trying to avoid the nest and hoping the query optimizer would work out that it would only have to run it once, do you know if this is the case?

as i told you the way you wrote it will evaluate correlated query for each of the rows and will apply top 1 only last. So if you want it to be applied once you need to take top 1 first and then apply apply correlated query which is what i showed you.