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.
Go to Top of Page

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.
Go to Top of Page

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!

Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

redapollos
Starting Member

1 Post

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

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -