September 19, 2013 at 5:12 am
Hi Folks,
Can someone help with the following on SQL Server 2008...
I have a table Customer and an Orders Table...
I want to return back against each customer the Order number from the last order placed (highest OrderDate)...
For example, if Customer X has 3 orders and Customer Y has 3 orders, I want X and Y customer names returned along with the the order number form each customer's last placed order.
Setup script for the 2 tables plus data as follows...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [int] NOT NULL,
[CustomerName] [varchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[OrderDate] [datetime] NULL,
[OrderNumber] [varchar](50) NULL,
[CustomerID] [int] NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [Customer] ([CustomerID],[CustomerName])
VALUES
(1,'David'),
(2,'John'),
(3,'Sue')
GO
INSERT INTO [Orders] ([OrderID],[OrderDate],[OrderNumber],[CustomerID])
VALUES
(1, '2013-01-01', '0001', 1),
(2, '2013-01-03', '0002', 1),
(3, '2013-02-12', '0003', 1),
(4, '2013-01-17', '0004', 2),
(5, '2013-01-18', '0005', 2),
(6, '2013-02-01', '0006', 2),
(7, '2013-01-14', '0007', 3),
(8, '2013-01-21', '0008', 3)
GO
What I'd Like back is...
'David', '0003'
'John', '0006'
'Sue', '0008'
So far I've got...
SELECT A.[CustomerName],
ISNULL(X.[MaxOrderNo], '') AS [LastOrderNumber]
FROM [Customer] A
LEFT OUTER JOIN (SELECT A.[CustomerID],
ISNULL(MAX(A.[OrderNumber]), '') AS [MaxOrderNo]
FROM [Orders] A
GROUP BY A.[CustomerID]) X ON X.[CustomerID] = A.[CustomerID]
However, although this gives the correct result with the current data, I need to check that the value from the order returned = the last order date as I can't guarentee that the highest order number for a customer has the highest date - this can be demonstrated by swapping the dates round on the last 2 orders (my query would give the wrong result)...
Many thanks for any help 🙂
September 19, 2013 at 5:24 am
This should give you what you're after.
with cte as (
select c.customername, o.ordernumber, o.orderdate, row_number() over (partition by c.customername order by orderdate desc) rownum
from customer c
inner join Orders o on o.customerid = c.customerid)
select customername, ordernumber
from cte
where rownum = 1;
September 19, 2013 at 5:39 am
Thanks Ed, that's given me what I needed. Many thanks.
😀
September 20, 2013 at 3:22 pm
Suspecting the CTE with an inner join and row numbering would be somewhat costly, I compared it against this:
SELECT c.customername, o.ordernumber, o.orderdate
FROM customer c
CROSS APPLY (SELECT TOP(1) ox.ordernumber, ox.orderdate FROM orders ox ORDER BY ox.orderdate DESC) o
On a test with 100,000 rows from a "customer" table that has a one-to-many relationship with an "order" table and an index on the "customername" in the "orders" table, the I/O was about the same, but the CROSS APPLY outperformed the INNER JOIN speedwise - it ran about three times faster.
This is probably because the CROSS APPLY generated a much better execution plan. The CROSS APPLY execution plan was just an index seek on the "orders" table followed by a TOP operator on that result and all inner joined to the "customer" table. The INNER JOIN performed a parallelized index seek on the "orders" table followed by an inner join to the customers table, then gathered the streams for segment and sequence operations (to accomplish the row number) then performed a parallelized filter operation to get the final result. All that bouncing back and forth between parallel and non-parallel processing gets costly.
Jason Wolfkill
September 20, 2013 at 3:44 pm
Hi Wolfkillj,
Many thanks to you also for taking the time to respond to my question. Unfortunately it doesn't seem to give me the correct result - all 3 customers have order 0003 returned against them. Any suggestions?
Regards Charlotte CB 😀
September 23, 2013 at 2:13 am
Does this work for your case?
WITH Orders AS
(
SELECT *
,rn=ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderID DESC)
FROM Orders
)
SELECT CustomerName, OrderNumber
FROM Orders a
JOIN Customer b ON a.CustomerID = b.CustomerID
WHERE rn=1;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 23, 2013 at 2:46 am
Hi dwain.c,
Yep that works for me - I'll scale up my data to see how it performs against the other solutions.
Many thanks for responding.:-D
September 23, 2013 at 2:58 am
Charlottecb (9/23/2013)
Hi dwain.c,Yep that works for me - I'll scale up my data to see how it performs against the other solutions.
Many thanks for responding.:-D
You are welcome and thanks for letting me know.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 23, 2013 at 7:46 am
Charlottecb (9/20/2013)
Hi Wolfkillj,Many thanks to you also for taking the time to respond to my question. Unfortunately it doesn't seem to give me the correct result - all 3 customers have order 0003 returned against them. Any suggestions?
Regards Charlotte CB 😀
Gah! In cutting and pasting the code, I left out a key piece - the WHERE clause in the correlated subquery (without which, it's just a subquery that's not correlated to anything!). Try this:
SELECT c.customername, o.ordernumber, o.orderdate
FROM customer c
CROSS APPLY (SELECT TOP(1) ox.ordernumber, ox.orderdate FROM orders ox WHERE c.customerID = ox.customerID ORDER BY ox.orderdate DESC) o
Sorry about that mistake!
Jason Wolfkill
September 23, 2013 at 8:43 am
wolfkillj (9/23/2013)
Charlottecb (9/20/2013)
Hi Wolfkillj,Many thanks to you also for taking the time to respond to my question. Unfortunately it doesn't seem to give me the correct result - all 3 customers have order 0003 returned against them. Any suggestions?
Regards Charlotte CB 😀
Gah! In cutting and pasting the code, I left out a key piece - the WHERE clause in the correlated subquery (without which, it's just a subquery that's not correlated to anything!). Try this:
SELECT c.customername, o.ordernumber, o.orderdateFROM customer c
CROSS APPLY (SELECT TOP(1) ox.ordernumber, ox.orderdate FROM orders ox WHERE c.customerID = ox.customerID ORDER BY ox.orderdate DESC) o
Sorry about that mistake!
Thanks wolfkillj:-D
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply