SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL);

INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0);
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0);
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson');
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole');
13 rows affected

DELETE D 
FROM
(
    SELECT
        r = ROW_NUMBER() OVER (
            PARTITION BY A.IDNum
            ORDER BY 
                CASE 
                    WHEN A.FirstName = '0' AND A.LastName = '0' THEN 1 
                    ELSE 0 
                END ASC)
    FROM dbo.Accounts AS A
) AS D
WHERE D.r > 1;
2 rows affected

SELECT
    A.IDNum,
    A.FirstName,
    A.LastName
FROM dbo.Accounts AS A
ORDER BY A.IDNum;


IDNum
FirstName
LastName


123
Joe
McDaniels

124
Mary
Cain

125
John
Johnson

126
Sue
Bird

127
Michael
Bennett

128
Larry
Jackson

129
0
0

130
Tanya
Hall

131
0
0

132
Joe
Johnson

133
Stephen
Cole

IDNum	FirstName	LastName
123	Joe	McDaniels
124	Mary	Cain
125	John	Johnson
126	Sue	Bird
127	Michael	Bennett
128	Larry	Jackson
129	0	0
130	Tanya	Hall
131	0	0
132	Joe	Johnson
133	Stephen	Cole