| Author |
 Topic  |
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
 Posted - 09/25/2007 : 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
USA
7423 Posts |
Posted - 09/25/2007 : 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
|
Edited by - jsmith8858 on 09/25/2007 10:59:40 |
 |
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 09/25/2007 : 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
USA
7423 Posts |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 09/25/2007 : 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
|
Edited by - jsmith8858 on 09/25/2007 11:11:33 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 09/25/2007 : 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
|
Edited by - jsmith8858 on 09/25/2007 11:11:52 |
|
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 09/25/2007 : 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
USA
7423 Posts |
Posted - 09/25/2007 : 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
|
Edited by - jsmith8858 on 09/25/2007 11:39:34 |
|
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 09/25/2007 : 11:48:38
|
| That's brilliant thanks. |
|
|
| |
Topic |
|
Need Cross Apply?
