Select 2 most recent orders for each customer

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 2000 Forums

Transact-SQL (2000)

Select 2 most recent orders for each customer

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2007-10-09 : 11:57:06

I have created the following query on the Northwind database.


Select OrderID, a.CustomerID, CompanyName, OrderDate 
from Customers a join Orders b on a.CustomerID = b.CustomerID
where b.OrderID in (select top 2 OrderID from Orders c where a.CustomerID = c.CustomerID order by c.OrderDate desc)
order by a.CustomerID, OrderDate desc

This same logic can be applied for a number of reports that I am doing and I am wondering if this is the most efficient way to do this? On a large recordset this takes a very long time. If this is the way do do it what indexes would improve the performance.

Thanks
scott

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 12:05:11

[code]-- Prepare staging
DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), CustomerID INT, CompanyName VARCHAR(30), OrderID INT)

INSERT @Stage
SELECT c.CustomerID,
c.CompanyName,
o.OrderID
FROM Customers AS c
INNER JOIN Orders AS o ON o.CustomerID = c.CustomerID
ORDER BY c.CustomerID,
o.OrderDate DESC

-- Show the expected output
SELECT s.CustomerID,
s.CustomerName,
s.OrderDate
FROM @Stage AS s
INNER JOIN (
SELECT MIN(RowID) AS LowRowID,
MIN(RowID) + 1 AS HighRowID
CustomerID
FROM @Stage
GROUP BY CustomerID
) AS d ON d.CustomerID = s.CustomerID
WHERE s.RowID BETWEEN d.LowRowID AND d.HighRowID[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 12:09:18

Could you double-join the [Orders] table to get the MAX(OrderID) [top value] and join again for the MAX(OrderID) WHERE NOT IN the first JOIN - second-top-value?

Kristen

Scott
Posting Yak Master

145 Posts

Posted - 2007-10-09 : 12:16:06

quote:
Originally posted by Kristen

Could you double-join the [Orders] table to get the MAX(OrderID) [top value] and join again for the MAX(OrderID) WHERE NOT IN the first JOIN - second-top-value?

Kristen

Perhaps but how would that scale if suddenly "they" wanted the most recent 5 orders?

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 13:57:37

I was going to raise the scalability issue, honest!

Scott
Posting Yak Master

145 Posts

Posted - 2007-10-09 : 17:16:00

This is how you would do it in SQL 2005:


SELECT 
	CustomerID,
	OrderID,
	Orderdate
FROM (
	SELECT
		O.CustomerID,
		O.OrderID, 
		O.OrderDate, 
		ROW_NUMBER = ROW_NUMBER() OVER (
			PARTITION BY O.CustomerID 
			ORDER BY O.OrderDate DESC)
	FROM	
		Orders AS O
	) AS d
WHERE
	d.ROW_NUMBER <= 3

Only wish there was a nice way to do it in SQL 2000. The performance difference between this statement and my original one is amazing.

Scott

Above statement from the article:
http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 22:40:03

Out of curiosity: how does Peso's suggestion perform? [on SQL 2000]

Might be worth trying a PRIMARY KEY constraint on either RowID or CustomerID in Peso's @Stage table - I can't figure out which is more likely to benefit though.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-10 : 02:15:12

Also, have a look at point 2

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources