SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 in select top 1 will outer apply be done on all		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alecwh22
Starting Member

United Kingdom
15 Posts
Posted - 03/11/2013 :  06:53:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 03/11/2013 :  07:03:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48012 Posts
Posted - 03/11/2013 :  07:09:20  Show Profile  Reply with Quote
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/
Go to Top of Page

alecwh22
Starting Member

United Kingdom
15 Posts
Posted - 03/11/2013 :  07:33:10  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48012 Posts
Posted - 03/11/2013 :  12:20:51  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000