How to have distinct data in SQL using MAX on inner joined tables

I used this code to get distinct columns according to the max update_date. But still I get about 4 or 5 status_ids for the same tel_number. I want the max update date to take only the last date...which is not currently done by my code. Can someone please help me

SELECT  DISTINCT t.Tel_Number,
        t.Entity_ID,
        t.Datasource,
        t.Datasource_Number,
        t.UpdateDate, 
        t.DataDate, 
        t.Telephone_ID,
        t.Status_Id, 
        t.DateInserted,
        t.ProcessName,
        c.Status_Id AS CurrentCe_Status_ID,
        s.StatusType AS CurrentCe_StatusType,
        s.Description AS CurrentCe_Status_Description,
        MAX(c.Update_Date) AS CurrentCe_Status_Date

FROM   
    Wrk.dbo.tel_trsn t WITH (NOLOCK) INNER JOIN CrWec.dbo.teldet d WITH (NOLOCK)
    ON d.Tel_Number = t.Tel_Number
    AND d.Entity_Id = t.Entity_ID
    INNER JOIN   CrWec.dbo.status c WITH (NOLOCK)
    ON c.Entity_Id = t.Entity_ID
    INNER JOIN CrWec.dbo.statusType s WITH (NOLOCK)
    ON s.Status_Id = c.Status_Id
GROUP BY t.Tel_Number,
        t.Entity_ID,
        t.Datasource,
        t.Datasource_Number,
        t.UpdateDate, 
        t.DataDate, 
        t.Telephone_ID,
        t.Status_Id, 
        t.DateInserted,
        t.ProcessName,
        c.Status_Id,
        s开发者_Python百科.StatusType,
        s.Description

Since you didn't specify what any of the keys were, I did the best I could with the query. In reality, if your key values are just Tel_Number, Entity_ID, Datasource, then you'd only need to partition on those 3 columns in the ROW_NUMBER function (or however many is necessary).

;with MaxUpdateDate as (
    SELECT  t.Tel_Number,
            t.Entity_ID,
            t.Datasource,
            t.Datasource_Number,
            t.UpdateDate, 
            t.DataDate, 
            t.Telephone_ID,
            t.Status_Id, 
            t.DateInserted,
            t.ProcessName,
            c.Status_Id AS CurrentCe_Status_ID,
            s.StatusType AS CurrentCe_StatusType,
            s.Description AS CurrentCe_Status_Description,
            c.Update_Date AS CurrentCe_Status_Date,
            ROW_NUMBER() OVER (
                PARTITION BY 
                    t.Tel_Number,
                    t.Entity_ID,
                    t.Datasource,
                    t.Datasource_Number,
                    t.UpdateDate, 
                    t.DataDate, 
                    t.Telephone_ID,
                    t.Status_Id, 
                    t.DateInserted,
                    t.ProcessName,
                    c.Status_Id,
                    s.StatusType,
                    s.Description 
                ORDER BY 
                    c.Update_Date DESC) as 'RowNum'
        FROM   
            Wrk.dbo.tel_trsn t WITH (NOLOCK) 
            INNER JOIN CrWec.dbo.teldet d WITH (NOLOCK)
                ON  d.Tel_Number = t.Tel_Number
                    AND d.Entity_Id = t.Entity_ID
            INNER JOIN CrWec.dbo.status c WITH (NOLOCK)
                ON  c.Entity_Id = t.Entity_ID
            INNER JOIN CrWec.dbo.statusType s WITH (NOLOCK)
                ON  s.Status_Id = c.Status_Id
)

SELECT
    *
FROM
    MaxUpdateDate
WHERE
    RowNum = 1