October 4, 2007 at 9:15 am
I'm looking to use a CTE to return the equivalent of the following query.
SELECT ID, CustName, (SELECT TOP (1) OrderDate AS [Last Order] FROM Orders WHERE CustID = Customers.ID ORDER BY OrderDate DESC)
FROM Customers
Thank you.
Kato Wilbur
October 4, 2007 at 9:27 am
WITH yourCTEname
AS
(SELECT
CustID
,MAX(OrderDate) AS [Last Order]
FROM Orders
WHERE CustID
GROUP BY CustID)
SELECT
ID,
CustName,
FROM Customers
LEFT JOIN yourCTEname
ON Customers.ID = yourCTEname.CustID
-- OR --
WITH yourCTEname
(CustID
,[Last Order])
AS
(SELECT
CustID
,MAX(OrderDate)
FROM Orders
GROUP BY CustID)
SELECT
ID,
CustName,
FROM Customers
LEFT JOIN yourCTEname
ON Customers.ID = yourCTEname.CustID
-- OR --
WITH yourCTEname
(CustID
,rn
,[Last Order])
AS
(SELECT
CustID
,rn = ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY OrderDate DESC)
,OrderDate
FROM Orders)
SELECT
ID,
CustName,
FROM Customers
LEFT JOIN yourCTEname
ON Customers.ID = yourCTEname.CustID
WHERE
rn = 1
FYI, it's good practice NOT to use spaces in field names.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 4, 2007 at 9:34 am
Thanks. I knew it'd be something simple. Forgot about MAX().
The [Last Order] thing is a habit I got into to make the column headers more readable. A lot of my users are confused by things like LastOrder. (sigh)
Kato
October 4, 2007 at 9:37 am
And if you haven't used the ROW_NUMBER keyword, take a dive into it. I've found it to be pretty useful. :w00t:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 4, 2007 at 9:39 am
I'll do that.
Thanks again.
Kato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply