How do I get the latest record for a particular customer number using ms-sql query (1 Viewer)

[rome123]

My table appears like this with the following columns:

Name| CUSTNo| ORderNo.| AcctDate| OrderDate| OrderDescription

I would like to write a ms-sql query to return the records of each customer number with the latest OrderDate. One more thing, if the customer has two separate order numbers on the same date(should be the latest date), the query should be able to pull up both the records. Thank You!

I tried with the code below but its taking a long time to execute and finally hanging up MS ACCESS.

SELECT * FROM TableName AS a WHERE Not Exists (SELECT * FROM TableName b WHERE b.CUSTNo = a.CUSTNo AND b.OrderDate >= a.OrderDate);

 

[plog]

I didn't test this, but the idea is sound:

SELECT * 
FROM TableName 
INNER JOIN 
    (SELECT CUSTNo, MAX(OrderDate) AS MostRecent 
    FROM TableName GROUP BY CUSTNo) AS MaxDate 
ON TableName.CUSTNo=MaxDate.CUSTNo 
    AND TableName.OrderDate=MaxDate.MostRecent;

 

[rome123]

thank you so much... This resolved my problem