Article: Using CROSS APPLY in SQL Server 2005

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

Site Related Forums

Article Discussion

Article: Using CROSS APPLY in SQL Server 2005

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-05 : 01:08:47

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

1961 Posts

Posted - 2005-05-05 : 04:43:27

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.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-05-05 : 08:47:03

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.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-05-05 : 12:14:12

Ah, I'd forgotten that little detail about ranking functions not working in WHERE expressions. Thanks!

vrapp
Starting Member

1 Post

Posted - 2005-10-29 : 08:42:04

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-29 : 09:53:44

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.

redapollos
Starting Member

1 Post

Posted - 2006-01-24 : 11:50:06

How would one accomplish this with sql server 2000?

ShenyiBao
Starting Member

1 Post

Posted - 2008-01-26 : 14:28:52

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

MrDavidOgle
Starting Member

1 Post

Posted - 2009-03-14 : 01:50:16

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

robajz
Starting Member

4 Posts

Posted - 2010-01-18 : 05:31:50

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

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 05:49:06

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-18 : 05:53:20

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

robajz
Starting Member

4 Posts

Posted - 2010-01-19 : 03:11:31

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

robajz
Starting Member

4 Posts

Posted - 2010-01-19 : 03:22:30

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)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-19 : 03:55:03

More methods
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-19 : 03:58:30

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)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-19 : 04:01:33

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

knightEknight
Starting Member

1 Post

Posted - 2010-07-05 : 22:37:06

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

MemeDeveloper
Starting Member

1 Post

Posted - 2010-07-28 : 03:16:08

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources