SELECT ModifiedBy ,IDCardNo
FROM (
SELECT ROW_NUMBER() over (PARTITION BY IDCardNo ORDER BY dt DESC) AS num ,
ModifiedBy,
IDCardNo
FROM test
WHERE ItemNo=@itemno AND ActiveFlag='Y'
) AS t WHERE t.num=1
SELECT ModifiedBy ,IDCardNo
FROM (
SELECT ROW_NUMBER() over (PARTITION BY IDCardNo ORDER BY dt DESC) AS num ,
ModifiedBy,
IDCardNo
FROM test
WHERE ItemNo=@itemno AND ActiveFlag='Y'
) AS t WHERE t.num=1