Home Forums SQL Server 2008 T-SQL (SS2K8) List out rows from a duplicate record set that has one or more odd values RE: List out rows from a duplicate record set that has one or more odd values

  • Sorry.. I didnt read the post completely, so here is the modified query,

    SET STATISTICS PROFILE ON

    SELECT aa.* FROM @mySampleTable aa

    INNER JOIN (SELECT a.CaseId, a.latestUpd FROM (SELECT CASEID, COUNT(*) CCnt, MAX(CreatedDate) LatestUpd FROM @mySampleTable GROUP BY CASEID) A OUTER APPLY (SELECT TOP 1 CASEID, STUDENTID, COUNT(*) SCnt FROM @mySampleTable B WHERE A.cASEiD = B.cASEiD GROUP BY CASEID, STUDENTID) AS BWHERE A.CCNT <> b.scnt) bb on aa.CaseId = bb.CaseId

    ORDER BY latestUpd desc, aa.CaseId

    I tried to avoid using Max and Min function on Char column as this may back fire with huge data. and used Outer Apply instead with Counting No Of Rows against each CaseID and StudentId