Show most recent information for a field

[dianemarie]

Hello. I’m trying to retrieve Last Broker for a particular order. I have an order history table (OH) with the usual info but for purposes here the three relevant fields are: Order#, Customer, Broker. A customer has many orders, and a customer also can use different brokers for each order. I am summarizing by customer (quantity) but I want to pull only the most recent broker that the customer used. This can always be accomplished by looking at max(order#) or max(OrdDate) – same diff. Right now, I have a temp table @T that pulls the most recent order for each customer, returning order#, broker# and customer# for the most recent order.
select max(OrdNum) as OrdNum,BrokerNum,CustNum
from nm_OrdHistAll OH
group by CustNum,BrokerNum
I thought I could link @T to OH this way as shown below, and then pull the field (LastBroker) for each order from the t.BrokerNum.
left outer join @T t on t.BrokerNum = oh.BrokerNum

What I expected to see in my result set is shown in the example below. But the LastBroker is not working (I’m still seeing the broker on the order) and also I’m getting duplicate records. Obviously I’m going about this wrong. Any help would be greatly appreciated.

OrdDate/oh.OrdNum/ oh.CustNum/oh.BrokerOnOrder /t.LastBroker
01/01/09 123 ABC Co. BrokerA BrokerC
01/05/09 128 ABC Co. BrokerB BrokerC
01/10/09 132 ABC Co. BrokerA BrokerC
02/06/09 155 ABC Co. BrokerC BrokerC

 

[RiverGuy]

Something like this:

SELECT 
OH.*, OH4.BrokerOnOrder AS LastBroker
FROM nm_OrdHistAll OH
INNER JOIN
  (SELECT OH2.*
   FROM nm_OrdHistAll OH2
   INNER JOIN
     (SELECT CustNum, MAX(OrdDate) AS MaxDate
      FROM nm_OrdHistAll) OH3 
   ON OH2.CustNum = OH3.CustNum
   AND OH2.OrdDate = OH3.MaxDate
   ) OH4
ON OH2.CustNum = OH4.CustNum

 

[dianemarie]

Hi RG, thanks for your help. This is what I now have:

SELECT lo.*,
lo4.client as LastBrokerID

FROM nm_ordhistall LO
inner join
(SELECT lo2.*
From nm_OrdHistAll LO2
inner join
(select client, max(createdate) as maxdate
from nm_ordhistall) lo3
on lo2.client = lo3.client
and lo2.createdate = lo3.maxdate) lo4
on lo2.client = lo4.client

But I am getting "nm_ordhistall.client is invalid in the select list becaust it is not contained in either an aggregate function or the group by clause.

 

[RiverGuy]

I forgot one part

SELECT  lo.*,
lo4.client as LastBrokerID

FROM   nm_ordhistall LO
inner join
    (SELECT  lo2.*
    From nm_OrdHistAll LO2
    inner join
        (select client, max(createdate) as maxdate
        from nm_ordhistall
        [b]GROUP BY client[/b]) lo3
    on lo2.client = lo3.client
    and lo2.createdate = lo3.maxdate) lo4
on lo2.client = lo4.client

 

[dianemarie]

Hi RG, still struggling. I added the Group By but now get "the multi-part identifier "LO2.client" could not be bound. Any thoughts?

 

[RiverGuy]

Bottom line should be

on LO.client = lo4.client