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

Question

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.StatusType,
        s.Description

You need HAVING to select in grouped results. Look here: http://stackoverflow.com/questions/5140785/mysql-order-before-group-by/5140943#5140943 — edze, Sep 01 '11 at 08:35
Look into the `ROW_NUMBER` function. I think you are asking about a [greatest-n-per-group](http://stackoverflow.com/questions/tagged/greatest-n-per-group+sql-server?sort=votes&pagesize=50) query. — Martin Smith, Sep 01 '11 at 12:06
I'm sure all the columns and tables you've got in this SELECT statement are necessary for your final query, but they are *not needed* for the basic problem you are asking about. Please simplify your query by including only the most necessary parts, because all those numerous columns/joins might just distract and discourage potential answerers. Also please do not hesitate adding some examples (sample data and corresponding output). — Andriy M, Sep 01 '11 at 17:28

score 0 · Answer 1 · answered Sep 15 '11 at 16:56

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

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

1 Answers1