Most recent order from multi join query

Here's the CTE version of the query, but it is returning multiple rows for the same customer. Not just the row containing the most recent open order date:

```
with LatestOPenOrderDate_CTE AS
(
Select Customer, OpenOrderDate = Max(OpenDate)
from Orders
group by Customer
)

select
o.Customer, Branch,
con.Name as Closer, o.GFNO,
o.UserDate1 as CancelDate,
OpenDate,

o.AgentSeller as SellerAgentNum,
dbo.wtAgentName(o.AgentSeller) as SellerAgentName,
dbo.wtAgentContactName(o.AgentSeller) as SellerContactName,

o.AgentBuyer as BuyerAgentNum,
dbo.wtAgentName(o.AgentBuyer) as BuyerAgentName,
dbo.wtAgentContactName(o.AgentBuyer) as BuyerContactName,

Lender,MortgageBrokerID,
Service,
OrderCategory, SalesPrice, LoanAmount,SourceOfBusiness,
dbo.wtGetPropertyField (o.GFNO, 'Address') As PropertyAddress,
dbo.wtGetPropertyField (o.GFNO, 'City') As PropertyCity,
dbo.wtGetPropertyField (o.GFNO, 'State') As PropertyState,
dbo.wtGetPropertyField (o.GFNO, 'Zip') As PropertyZip,

Case
when SourceOfBusiness like '%Selling Realtor%' then dbo.wtagentname(o.agentbuyer)
when SourceOfBusiness like '%Listing%' then dbo.wtagentname(o.AgentSeller)
when SourceOfBusiness like '%Lender%' then dbo.wtGetAgentField(Lender,'Name')
when SourceOfBusiness like '%Mortgage Broker%' then dbo.wtGetAgentField(MortgageBrokerID,'Name')
else
''
end as PDCContactName,

Case
when SourceOfBusiness like '%Selling Realtor%' then dbo.wtGetAgentField(o.agentbuyer,'Phone')
when SourceOfBusiness like '%Listing%' then dbo.wtGetAgentField(o.AcctBalance,'Phone')
when SourceOfBusiness like '%Lender%' then dbo.wtGetAgentField(Lender,'Phone')
when SourceOfBusiness like '%Mortgage Broker%' then dbo.wtGetAgentField(MortgageBrokerID,'Phone')
else
''
end as PDCPhone,

Case
when SourceOfBusiness like '%Selling Realtor%' then dbo.wtGetAgentField(o.a

The following pattern will often render very good performance, compared to self-joins and correlating subqueries:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY CustomerID, c1, c2, c3
        ORDER BY OpenOrderDate DESC
        ) AS _rownum
    FROM some_table
    INNER JOIN ...
    INNER JOIN ...
    WHERE ...
    ) AS sub
WHERE sub._rownum=1;

The ROW_NUMBER() function assigns a sequential number ordered by OpenOrderDate descending. By filtering on the output of this function, you can isolate the single-most recent row for each partition. Note the PARTITION BY clause, which defines how and when the row number function resets to 1.

Like @Erik points out, make sure that the combination of the PARTITION BY and ORDER BY columns generate a unique key.

StackExchange Database Administrators Q#126415, answer score: 2