Get latest record for each customer

Transact-SQL
1

Just trying to get the last transaction for each customer based the sales date and I just can't get it right. I need one record for each customer. I will be joining this table on a customers table on custno. I realize this is a fairly simple query but I am having trouble with it. Could someone offer some assistance please?

Thanks
Bill

2

I have tried this but get an error:

Select top 1 hist.ORDER_CUST_NO,
hist.ORDER_SALES_REP_NO,
convert(char(10),hist.ORD_DATE, 101) as LastSalesDate
from wo_Sales_Order_History hist
where hist.ORD_DATE= (Select max(hist.ORD_DATE) from wo_Sales_Order_History hist_1)
and hist.ORDER_SALES_REP_NO= 'ABC'

3 
SELECT
    hist.ORDER_CUST_NO ,
    hist.ORDER_SALES_REP_NO ,
    MAX(hist.ORD_DATE) AS LastSalesDate
FROM
	wo_Sales_Order_History hist
WHERE
	hist.ORDER_SALES_REP_NO = 'ABC';
GROUP BY
    hist.ORDER_CUST_NO ,
    hist.ORDER_SALES_REP_NO;
4

If sales rep is not used in your calculation then maybe:

SELECT h1.ORDER_CUST_NO, h1.ORDER_SALES_REP_NO, h1.ORD_DATE FROM ( SELECT hist.ORDER_CUST_NO, hist.ORDER_SALES_REP_NO, hist.ORD_DATE ROW_NUMBER() OVER (PARTITION BY hist.ORDER_CUST_NO ORDER BY hist.ORD_DATE DESC) RN FROM wo_Sales_Order_History hist --WHERE hist.ORDER_SALES_REP_NO = 'ABC' ) H1 WHERE RN = 1;

Note that if you provide the error message it helps us help you.

5

DJJ55:
Yes, sorry I omitted that. JamesK's solution worked. Thanks very much for your time and effort

6

JamesK:
Thanks very much, that's what I needed

7

I have used both solutions, but was too late to help. Call this an "endorsement"...