Q:

SQL: Why is distinct and max not removing duplicates?

SHouldn't the following query remove duplicates:
SELECT DISTINCT Relevant.PropertyID, ACC.TenancyStartDate, ACC.AccountID, ACC.TenancyType
FROM            DimAccount AS ACC RIGHT OUTER JOIN
                             (SELECT DISTINCT PropertyID, MAX(TenancyStartDate) AS Tenancystart
                               FROM            DimAccount
                               WHERE        (AccountStatus = 'Current')
                               GROUP BY PropertyID, TenancyStartDate) AS Relevant ON ACC.PropertyID = Relevant.PropertyID AND ACC.TenancyStartDate = Relevant.Tenancystart
GROUP BY Relevant.PropertyID, ACC.TenancyStartDate, ACC.AccountID, ACC.TenancyType, ACC.TenancyType

From my understanding (and what I want to happen) is, the query in brackets is selecting the property ID and of the ones with a status of current returning the highest tenancy start date (albeit several times). This is then joined to the original table by start date and property id, to get the most recent tenancytype.
Why is it still returning duplicate lines!?
(by the way this is relating to another question I had yesterday, but apparently replies are not supposed to descend into conversation so I thought I'd seperate this off... I hope that is the right thing to do... I have searched but clearly there is something missing in my understanding of something!)

A:

First, you almost never need select distinct when using group by.
The problem with your query is the group by clause in the subquery.
SELECT Relevant.PropertyID, ACC.TenancyStartDate, ACC.AccountID, ACC.TenancyType
FROM DimAccount ACC RIGHT OUTER JOIN
     (SELECT PropertyID, MAX(TenancyStartDate) AS Tenancystart
      FROM  DimAccount
      WHERE (AccountStatus = 'Current')
      GROUP BY PropertyID
    ) Relevant
     ON ACC.PropertyID = Relevant.PropertyID AND
        ACC.TenancyStartDate = Relevant.Tenancystart
GROUP BY Relevant.PropertyID, ACC.TenancyStartDate, ACC.AccountID, ACC.TenancyType;

It should not have TenancyStartDate.  Also, your outer query had ACC.TenancyType twice in the group by.
That said, it is easier to write the query using analytic functions:
select a.*
from (select a.*,
             max(tenancystartdate) over (partition by propertyid) as max_tsd
      from dimaccount a
      where accountstatus = 'Current'
     ) a
where tenancystartdate = max_tsd;

This is not exactly the same as your query, because your query will take non-current records into account.  I am guessing that this might be the intention, however.

