Получите дубликат топ-1 набора из таблицы

Похоже на «разворот» классики Получите по 1 верхнему ряду в каждой группе. Сначала получите строки, содержащие дубликаты, используя COUNT, а затем получите первую из этих строк, используя (DENSE_)RANK (вместо ROW_NUMBER):

CREATE TABLE dbo.YourTable (Id int,
                            Name char(2),
                            Mobile varchar(13),
                            Email nvarchar(50));

INSERT INTO dbo.YourTable
VALUES(1,'k1','900000000','[email protected]         '),
      (2,'k2','900000001','[email protected]         '),
      (3,'k1','900000000','[email protected]   '),
      (4,'k2','900000001','[email protected] '),
      (5,'k1','900000000','[email protected]');
GO

WITH C AS(
    SELECT Id,
           Name,
           Mobile,
           Email,
           COUNT(*) OVER (PARTITION BY Name) AS Cnt --Or perhaps (PARTITION BY Name, Mobile, Email) based on latest revision
    FROM dbo.YourTable),
R AS(
    SELECT Id,
           Name,
           Mobile,
           Email,
           RANK() OVER (ORDER BY Name) AS Rnk --Or perhaps (ORDER BY Name, Mobile, Email) based on latest revision
    FROM C
    WHERE Cnt > 1)
SELECT Id,
       Name,
       Mobile,
       Email
FROM R
WHERE Rnk = 1;
GO

DROP TABLE dbo.YourTable;
GO