in select top 1 will outer apply be done on all

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

in select top 1 will outer apply be done on all

Author

Topic

alecwh22
Starting Member

15 Posts

Posted - 2013-03-11 : 06:53:31

in the query

select top 1 p.pid, p.name, numberOfSocks
from myDB.dbo.people p
outer apply
(
select sum(numberOfSocks) numberOfSocks
from myDb.dbo.whoHasScks ws
where p.pid = ws.pid
) ws
order by p.pid, p.name

will the outer apply be done for all records despite it not being in the order by meaning it only needs to be done for the top record?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-11 : 07:03:38

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

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 07:09:20

i think what you're after is this


select p.*,numberOfSocks
from
(
select top 1 pid, name
from myDB.dbo.people 
order by p.pid, p.name
)p
outer apply
(
select sum(numberOfSocks) numberOfSocks
from myDb.dbo.whoHasScks ws
where p.pid = ws.pid
) ws

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

alecwh22
Starting Member

15 Posts

Posted - 2013-03-11 : 07:33:10

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 12:20:51

quote:
Originally posted by alecwh22

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.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources