One row of data for a max date only - transact SQL

I am trying to select the max dates on a field with other tables, to only give me one distinct row for the max date and not other rows with other dates. the code i have for max is

SELECT DISTINCT                  
Cust.CustId,                      
LastDate=(Select Max(Convert(Date,TreatmentFieldHstry.Trea开发者_如何学PythontmentDateTime))   
          FROM TreatmentFieldHstry  
          WHERE Cust.CustSer = Course.CustSer  
          AND Course.CourseSer = Session.CourseSer 
          AND Session.SessionSer = TreatmentFieldHstry.SessionSer) 

This gives multiple rows depending on how many dates - i just want one for the max - can anyone help with this?

Thanks

---

You didn't specify exactly what database and version you're using - but if you're on SQL Server 2005 or newer, you can use something like this (a CTE with the ROW_NUMBER ranking function) - I've simplified it a bit, since I don't know what those other tables are that you have in your select, that don't ever show up in any of the SELECT column lists.....

;WITH TopData AS
(
    SELECT c.CustId, t.TreatmentDateTime,
       ROW_NUMBER() OVER(PARTITION BY c.CustId ORDER BY t.TreatmentDateTime DESC) AS 'RowNum'
    FROM 
        dbo.TreatmentFieldHstry t
    INNER JOIN
        dbo.Customer c ON c.CustId = t.CustId  -- or whatever JOIN condition you have
    WHERE 
        c.CustSer = Course.CustSer  
 )
 SELECT
     *
 FROM
     TopData
 WHERE
     RowNum = 1

Basically, the CTE (Common Table Expression) partitions your data by CustId and order by TreatmentDateTime (descending - newest first) - and numbers every entry with a consecutive number - for each "partition" (e.g. for each new value of CustId). With this, the newest entry for each customer has RowNum = 1 which is what I use to select it from that CTE.