gistfile1.txt

SELECT 
    @id = CA.Id, 
    @Ed = CA.EffectiveDate, 
    @sid = CA.SequenceId, 
    @cid = CA.CustomerId, 
    @ano = CA.AccountNo
FROM 
(
    -- Per Id
    SELECT DISTINCT Id 
    FROM dbo.TestTable
) AS TT
CROSS APPLY
(
    -- Single row with the highest EffectiveDate then SequenceId
    SELECT TOP (1) TT2.*
    FROM  dbo.TestTable AS TT2
    WHERE TT2.Id = TT.Id
    ORDER BY TT2.EffectiveDate DESC, TT2.SequenceId DESC
) AS CA;