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
 Site Related Forums
 Article Discussion
 Article: Using CROSS APPLY in SQL Server 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/05/2005 :  01:08:47  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
My interest in writing this article was started by an MSDN article titled SQL Server 2005: The CLR Enters the Relational Stage. The article shows how to write a function that returns the top three countries per category. That's always been something that was difficult to do in SQL so I was curious about the approach. The article started out well but I was very unhappy by the end. It's just soooo much easier to do this in SQL Server 2005 using the new CROSS APPLY clause in Transact-SQL. So I'm going to write a query to return the top 3 orders for each customer and I'm going to do it in about 10 lines of SQL.

Article Link.

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 05/05/2005 :  04:43:27  Show Profile  Reply with Quote
I remember being alternately amused and appalled while reading that MSDN article a few weeks ago. Anyway, maybe I'm being thick here, but what's wrong with just doing this?

SELECT C.CustomerID, O.SalesOrderID, O.TotalDue
FROM AdventureWorks.Sales.Customer AS C
INNER JOIN AdventureWorks.Sales.SalesOrderHeader AS O
  ON C.CustomerID = O.CustomerID
WHERE ROW_NUMBER() OVER (
  PARTITION BY C.CustomerID ORDER BY O.TotalDue DESC) <= 3
ORDER BY C.CustomerID ASC, O.TotalDue DESC

I don't have a SQL Server 2005 installation, so I can't test it, but that's what I understood to be the whole point of the ranking functions.

Edited by - Arnold Fribble on 05/05/2005 06:25:22
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 05/05/2005 :  08:47:03  Show Profile  Visit graz's Homepage  Reply with Quote
Arnold,

Why does it not suprise me that it was you that found this? :) I've updated the article to include your query. Thanks!



===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 05/05/2005 :  12:14:12  Show Profile  Reply with Quote
Ah, I'd forgotten that little detail about ranking functions not working in WHERE expressions. Thanks!

Go to Top of Page

vrapp
Starting Member

USA
1 Posts

Posted - 10/29/2005 :  08:42:04  Show Profile  Visit vrapp's Homepage  Send vrapp an ICQ Message  Reply with Quote
The real magic happens when you use SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function.

You could join a table to a table-valued function in sql2k with regular join.

...FROM
AdventureWorks.Sales.Customer AS C
INNER JOIN
AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) O
on c.customerid=c.customerid


Where's the magic?

Vadim Rapp
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/29/2005 :  09:53:44  Show Profile  Reply with Quote
quote:
Originally posted by vrapp
You could join a table to a table-valued function in sql2k with regular join.


No you can't. The table-valued functions in SQL Server 2000 accept only constants or @local_variable arguments.
Go to Top of Page

redapollos
Starting Member

1 Posts

Posted - 01/24/2006 :  11:50:06  Show Profile  Reply with Quote
How would one accomplish this with sql server 2000?
Go to Top of Page

ShenyiBao
Starting Member

1 Posts

Posted - 01/26/2008 :  14:28:52  Show Profile  Reply with Quote
I don't know why following query is much slower than calling a table valued function. Any ideas?

--Solution 2
SELECT
C.CustomerID,
d.SalesOrderID, -- it should be shown up in sub query, like O.SalesOrderID
d.TotalDue
from AdventureWorks.Sales.Customer AS C
cross apply
(
SELECT
top 3
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.SalesOrderHeader AS O
order by C.CustomerID ASC, O.TotalDue DESC
) AS d
go
Go to Top of Page

MrDavidOgle
Starting Member

USA
1 Posts

Posted - 03/14/2009 :  01:50:16  Show Profile  Visit MrDavidOgle's Homepage  Click to see MrDavidOgle's MSN Messenger address  Send MrDavidOgle a Yahoo! Message  Reply with Quote
Dood! You ROCK! I didn't even know about CROSS APPLY! Thank you sooo much!

Im adding the following code so others who are... as good as i am ... will know they can use a cross apply to fix the following error:

This doesnt work: The multi-part identifier "AssociateTeamMember.TeamMemberAssociateID" could not be bound.

SELECT AssociateTeamMemberKey,TeamMemberAssociateID,AssociateD.AssociateID, AssociateD.NameFirst, AssociateD.NameLast
FROM AssociateTeamMember
INNER JOIN (SELECT TOP (1) AssociateID, NameFirst, NameLast
FROM Associate AS Associate_1
WHERE (AssociateID = AssociateTeamMember.TeamMemberAssociateID)
ORDER BY AssociateEffectiveDate DESC) AS AssociateD ON TeamMemberAssociateID = AssociateD.AssociateID

1 - change INNER JOIN to CROSS APPLY
2 - remove ON TeamMemberAssociateID = AssociateD.AssociateID
change it to the following and it will work:

SELECT AssociateTeamMemberKey,TeamMemberAssociateID,AssociateD.AssociateID, AssociateD.NameFirst, AssociateD.NameLast
FROM AssociateTeamMember
CROSS APPLY (SELECT TOP (1) AssociateID, NameFirst, NameLast
FROM Associate AS Associate_1
WHERE (AssociateID = AssociateTeamMember.TeamMemberAssociateID)
ORDER BY AssociateEffectiveDate DESC) AS AssociateD

Edited by - MrDavidOgle on 03/14/2009 01:59:23
Go to Top of Page

robajz
Starting Member

4 Posts

Posted - 01/18/2010 :  05:31:50  Show Profile  Reply with Quote
Hi, not sure if this is just 2008 feature, but I'm able to do this:

select t1.id, t2sq.id
from t1
left join (
select top 3 *
from t2
order by t2.id desc
) t2sq
on t2sq.t1_id = t1.id

magic?
Rob
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/18/2010 :  05:49:06  Show Profile  Reply with Quote
Unless I've overlooked some subtle detail in your example I reckon you can do that in SQL 2000, probably SQL 7 even, rather than specifically only later versions.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 01/18/2010 :  05:53:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by AskSQLTeam

My interest in writing this article was started by an MSDN article titled <a href="http://msdn.microsoft.com/SQL/default.aspx?pull=/library/en-us/dnreal/html/realworld03112005.asp">SQL Server 2005: The CLR Enters the Relational Stage</a>. The article shows how to write a function that returns the top three countries per category. That's always been something that was difficult to do in SQL so I was curious about the approach. The article started out well but I was very unhappy by the end. It's just soooo much easier to do this in SQL Server 2005 using the new CROSS APPLY clause in Transact-SQL. So I'm going to write a query to return the top 3 orders for each customer and I'm going to do it in about 10 lines of SQL.<P>Article <a href="/item.asp?ItemID=21502">Link</a>.


The article links leads to " The page cannot be displayed" error

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

robajz
Starting Member

4 Posts

Posted - 01/19/2010 :  03:11:31  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

Unless I've overlooked some subtle detail in your example I reckon you can do that in SQL 2000, probably SQL 7 even, rather than specifically only later versions.



Kirsten, I'm just not sure you can get top N rows ordered from a subquery in 2k, I had a feeling :) that this was one of the new features, but it was probably wrong. This sample I've only tried on 2k8. Cheers, Rob
Go to Top of Page

robajz
Starting Member

4 Posts

Posted - 01/19/2010 :  03:22:30  Show Profile  Reply with Quote
quote:
Originally posted by robajz


select t1.id, t2sq.id
from t1
left join (
select top 3 *
from t2
order by t2.id desc
) t2sq
on t2sq.t1_id = t1.id



Just realized this was a pretty dumb mistake :) It should instead be like this:

select t1.id, t2.id
 from t1
 left join t2
	on t2.t1_id = t1.id
 where t2.id is null or t2.id in
   (select top 3 id
		from  t2
		where t1_id = t1.id
		order by t1.id desc)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 01/19/2010 :  03:55:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
More methods
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/19/2010 :  03:58:30  Show Profile  Reply with Quote
I substituted Table and Column names in your first query and ran it against SQL 2000 here - ran fine - so I don;t think the syntax will be a problem.

Your second style should be fine too (haven't looked at the logic closely though)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 01/19/2010 :  04:01:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

I substituted Table and Column names in your first query and ran it against SQL 2000 here - ran fine - so I don;t think the syntax will be a problem.

Your second style should be fine too (haven't looked at the logic closely though)


Are you replying to me?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

knightEknight
Starting Member

USA
1 Posts

Posted - 07/05/2010 :  22:37:06  Show Profile  Reply with Quote
Since CROSS APPLY is like doing a "join without an ON", can't the same results be achieved by doing a comprehensive join like this?


SELECT C.CustomerID,
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.Customer AS C
JOIN
AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
ON 1=1
ORDER BY
C.CustomerID ASC, O.TotalDue DESC

Go to Top of Page

MemeDeveloper
Starting Member

1 Posts

Posted - 07/28/2010 :  03:16:08  Show Profile  Reply with Quote
I've just recently found the APPLY opperator, and find it very very useful, but don't use it with TVFs I tend to use it with SELECTS from Views. Like this http://blog.agileclarity.com/archive/2010/07/22/use-sql-apply-to-order-by-top-x-and-join.aspx - which I find handy to do things with derived tables I couldn't get working easily before.

Anyone know why MS don't cover this sort of use in the documentation?

Edited by - MemeDeveloper on 07/28/2010 03:23:17
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.12 seconds. Powered By: Snitz Forums 2000