January 5, 2017 at 5:25 pm
In a sql server 2012 database, I have the following 2 tables:
1.
CREATE TABLE [dbo].[Orders](
[orderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CustID] [int] NOT NULL,
[comments] [varchar](20) NULL,
[PrintedDate] [datetime] NULL
)
2.
CREATE TABLE [dbo].[Customer](
[CustID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[laddress] [varchar](200) NULL,
[modifiedByID] [int] NOT NULL
)
I want to join the customer table to the orders table and select the most current printdate for a customer
from the orders table using a where clause potentially and not using the max(PrintedDate) on a select statement.
There should only be 1 record from the orders table selected.
I would like to accomplish this goal due to addtional tables that I will need to add to the l sql
that I listed here.
Thus the start of the sql would be:
select Customer.CustID,Orders.CustID,Orders.PrintedDate
from Customer as Customer
join Orders as Orders
on Orders.CustID = Customer.CustID
where Orders.PrintedDate is the most current date?
Thus would you should be how to have only the record from the orders table with the most
current print date using potential:
a. where clause,
b. having clause,
c. cte,
d. temp table,
and/or any other option you think would work and not using the max(PrintedDate) on a select statement?
January 5, 2017 at 8:54 pm
Does this do it for you? You don't have to join with Customer table unless you are missing a FK, which is not a good practice anyway.
;WITH cte AS
(
select c.CustID, o.PrintedDate, ROW_NUMBER() OVER(PARTITION BY c.CustID ORDER BY o.PrintedDate DESC) [rn]
from Customer c
join Orders o
on o.CustID = c.CustID
)
SELECT cte.CustID, cte.PrintedDate
FROM cte
WHERE rn = 1
https://sqlroadie.com/
January 5, 2017 at 11:04 pm
Here are 3 different approaches that will acheive the same results
-- Using cte with ROW_NUMBER
WITH cte AS (
SELECT CustID, PrintedDate, rn = ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY PrintedDate DESC)
FROM Orders
)
SELECT Customer.CustID, Orders.CustID, cte.PrintedDate
FROM Customer as Customer
INNER JOIN Orders as Orders
ON Orders.CustID = Customer.CustID
INNER JOIN cte
ON cte.CustID = Orders.CustID
AND cte.PrintedDate = Orders.PrintedDate
WHERE cte.rn = 1;
-- Using cte with GROUP BY
WITH cte AS (
SELECT CustID, PrintedDate = MAX(PrintedDate)
FROM Orders
GROUP BY CustID
)
SELECT Customer.CustID, Orders.CustID, cte.PrintedDate
FROM Customer as Customer
INNER JOIN Orders as Orders
ON Orders.CustID = Customer.CustID
INNER JOIN cte
ON cte.CustID = Orders.CustID
AND cte.PrintedDate = Orders.PrintedDate;
-- Using subquery with GROUP BY
SELECT Customer.CustID, Orders.CustID, cte.PrintedDate
FROM Customer as Customer
INNER JOIN Orders as Orders
ON Orders.CustID = Customer.CustID
INNER JOIN (SELECT CustID, PrintedDate = MAX(PrintedDate)
FROM Orders
GROUP BY CustID
) AS cte
ON cte.CustID = Orders.CustID
AND cte.PrintedDate = Orders.PrintedDate;
January 6, 2017 at 7:07 am
I've worked with this pattern many times over the years. I think these options will tend to evaluate ALL orders in the system, since the ROW_NUMBER calculations need to figure out which are most recent per customer. If each customer can have many orders it might make more sense to take the following approach:
Have a supporting index on the Orders table
CREATE NONCLUSTERED INDEX IX_Orders_CustID_PrintedDate ON dbo.Orders (CustID, PrintedDate DESC) INCLUDE (OrderID);
then use a CROSS APPLY (or OUTER APPLY if you want to include the case where a customer may not have an order yet)
SELECT C.CustID, cte.OrderID, cte.PrintedDate
FROM Customer AS C
CROSS APPLY
(SELECT TOP 1 O.OrderID, O.PrintedDate
FROM Orders O
WHERE O.CustID = C.CustID
ORDER BY O.PrintedDate DESC) cte;
This turns the join and sort on the Orders table into a very quick SEEK operation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply