Discussion on: Remove Duplicate Records via SQL Server's Rank Function

[allanford17]

    OUTER APPLY (SELECT TOP 1

also useful for this ..

e.g.

    OUTER APPLY (SELECT TOP 1
        ClaimNo, ClaimLineNo, ProviderID, AgreementID, CampusID, Version, BenefitID, SvcClass, SetNo, SeqNo
        FROM   #Claimlines CL
        WHERE
            E.Episodeid = CL.Episodeid
        AND CL.Admitdate IS NOT NULL
        AND CL.Dischargedate IS NOT NULL
        AND CL.Facilitylevel IS NOT NULL
        AND CL.Dischargedate <=
                    (
                      SELECT MIN(Dischargedate)
                      FROM #Claimlines
                      WHERE Episodeid = CL.Episodeid
                        AND Admitdate IS NOT NULL
                        AND Dischargedate IS NOT NULL
                        AND Facilitylevel IS NOT NULL
                     )
        ORDER BY
            CL.SetNo,
            CL.SeqNo,
            CL.ClaimNo,
            CL.ClaimLineNo
        )  CL