SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TES(
ID int,
Name Varchar(10)
)
Insert into TES (ID,NAME) VALUES (100,'XYZ')
Insert into TES (ID,NAME) VALUES (101,'XYZ')

CREATE TABLE [dbo].[TEST](
    [ORGORDER] [int] NULL,
    [Id] [int] NOT NULL,
    [ORGTYPE] [varchar](30) NULL,
    ORGID INT NULL,
    [LEAD] [decimal](19, 2) NULL
);

INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE] ,ORGID, [LEAD]) VALUES (1, 100, N'ABC',1, NULL)
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 100, N'ABC',2, 0)
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 100, N'ACD',1, NULL)
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 101, N'ABC',0, 0)
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (2, 101, N'ABC',4, NULL)
7 rows affected

with data as (
    SELECT TES.NAME, TEST.*,
        row_number() over (
            partition by TEST.ID
            order by case when TEST.ORGID = 1 then 1 else 2 end,
                     TEST.ORGORDER
        ) as rn
    FROM TES INNER JOIN TEST on TEST.ID = TES.ID
    WHERE TEST.ORGTYPE = 'ABC'
)
select ID, ORGORDER, ORGTYPE, ORGID, "LEAD" from data where rn = 1;


ID
ORGORDER
ORGTYPE
ORGID
LEAD


100
1
ABC
1
null

101
0
ABC
0
0.00


-- this clearly doesn't handle the extra ACD orgtype but not sure what that means
select TEST.ID, TEST.ORGORDER, TEST.ORGTYPE, TEST.ORGID,
    case when TEST.ORGID = 1 then TEST."LEAD"
         else (select "LEAD" from TEST where ID = TEST2.ID and ORGORDER = TEST2.MIN_ORGORDER) end as "LEAD"
from TES inner join TEST on TEST.ID = TES.ID
    inner join (
        select ID,
            min(ORGORDER) as MIN_ORGORDER,
            max(case when ORGID = 1 then 'Y' else 'N' end) as HAS_ORGID1
        from TEST
        group by ID
    ) as TEST2 on TEST2.ID = TEST.ID
where TEST.ORGID = 1 or TEST2.HAS_ORGID1 = 'N' and TEST2.MIN_ORGORDER = TEST.ORGORDER;


ID
ORGORDER
ORGTYPE
ORGID
LEAD


100
1
ABC
1
null

100
0
ACD
1
null

101
0
ABC
0
0.00