Need Cross Apply? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need Cross Apply?

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-09-25 : 10:52:37

Why do this?
SELECT C.customerid, city, orderid
FROM dbo.Customers AS C
CROSS APPLY
(SELECT TOP(2) orderid, customerid
FROM dbo.Orders AS O
WHERE O.customerid = C.customerid
ORDER BY orderid DESC) AS CA

When you can do this?
SELECT C.customerid, city, orderid
FROM dbo.Customers AS C
join
(SELECT TOP(2) orderid, customerid
FROM dbo.Orders
ORDER BY orderid DESC) AS CA on CA.customerid=C.customerid

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-25 : 10:58:14

Because they are not the same? The second will only return results for (at most) two customers, based on the last two orders (ordered by OrderID DESC). Try it.

Better is to simply use ROWNUMBER() or RANK() over OrderID and filter where they are <= 2.

- Jeff
http://weblogs.sqlteam.com/JeffS

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-09-25 : 11:01:18

quote:
Originally posted by jsmith8858

The second will only return results for (at most) two customers

That's the same for CROSS APPLY too isn't it?
Aren't you thinking of OUTER APPLY?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-25 : 11:02:03

Did you try it?

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-25 : 11:07:34

Here they are, using Adventure Works:

SELECT C.CustomerID, C.CustomerType, CA.SalesOrderID
FROM Sales.Customer AS C
CROSS APPLY
(SELECT TOP 2 O.SalesOrderID, O.CustomerID
FROM Sales.SalesOrderHeader AS O
WHERE O.CustomerID = C.CustomerID
ORDER BY SalesOrderID DESC) AS CA

SELECT C.CustomerID, C.CustomerType, CA.SalesOrderID
FROM Sales.Customer AS C
INNER JOIN
(SELECT TOP 2 O.SalesOrderID, O.CustomerID
FROM Sales.SalesOrderHeader AS O
ORDER BY SalesOrderID DESC) AS CA on CA.CustomerID = C.CustomerID

notice the results returned ...

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-25 : 11:11:00

And here's the ROW_NUMBER version (same results as CROSS APPLY)

SELECT x.* FROM
(
SELECT C.CustomerID, C.CustomerType, O.SalesOrderID, ROW_NUMBER() OVER (PARTITION BY C.CustomerID ORDER BY O.SalesOrderID DESC) as [Rank]
FROM Sales.Customer AS C
INNER JOIN
Sales.SalesOrderHeader AS O on C.CustomerID = O.CustomerID
) x
where x.Rank <=2

- Jeff
http://weblogs.sqlteam.com/JeffS

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-09-25 : 11:29:11

I temporarily didn't have access to Management Studio so couldn't test it. I have now and I see your point.

So could you accomplish the same output as:
SELECT C.CustomerID, C.CustomerType, CA.SalesOrderID
FROM Sales.Customer AS C
CROSS APPLY
(SELECT TOP 2 O.SalesOrderID, O.CustomerID
FROM Sales.SalesOrderHeader AS O
WHERE O.CustomerID = C.CustomerID
ORDER BY SalesOrderID DESC) AS CA

without using the APPLY table operator or ROW_NUMBER? Just old-school set-based querying (without loops, #temp tables etc)?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-25 : 11:38:29

Yes, but it will not be as efficient. either something like this:

select customer.customerID, orders.orderID,
from customers
inner join orders on customers.customerID = orders.customerID
where orderID in (select top 2 orderID from orders where orders.customerID = customers.CustomerID order by orderID DESC)

or by calculating the rank/row number manually like this:

select * from
(
select customer.customerID, orders.orderID,
(select count(*) from orders o2 where o2.orderID >= orders.orderID and o2.customerID = customer.CustomerID) as Rank
from customers
inner join orders on customers.customerID = orders.customerID
) r
where r.Rank <= 2

more here: http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-09-25 : 11:48:38

That's brilliant thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources