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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need Cross Apply?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 09/25/2007 :  10:52:37  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 09/25/2007 :  10:58:14  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 09/25/2007 10:59:40
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 09/25/2007 :  11:01:18  Show Profile  Reply with Quote
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?
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/25/2007 :  11:02:03  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Did you try it?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/25/2007 :  11:07:34  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 09/25/2007 11:11:33
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/25/2007 :  11:11:00  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 09/25/2007 11:11:52
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 09/25/2007 :  11:29:11  Show Profile  Reply with Quote
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)?
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/25/2007 :  11:38:29  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 09/25/2007 11:39:34
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 09/25/2007 :  11:48:38  Show Profile  Reply with Quote
That's brilliant thanks.
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.09 seconds. Powered By: Snitz Forums 2000