SQL Server 2019

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

CREATE TABLE Clients
(
OID INT,
Client_name VARCHAR(20) NOT NULL,
Client_central VARCHAR(20) NOT NULL,
Client_assigned_to INT NOT NULL,
);
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (1, 'Client_1', 'True', '');
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (2, 'Client_2', 'False', 1);
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (3, 'Client_3', 'True', '');
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (4, 'Client_4', 'False', 2);
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (5, 'Client_5', 'False', '');
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (6, 'Client_6', 'False', '');
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (7, 'Client_7', 'False', 1);
INSERT Clients(OID, Client_name, Client_central, Client_assigned_to) 
VALUES (8, 'Client_8', 'False', 2);

SELECT

CASE 
WHEN Clients.Client_central = 'True' THEN Clients.Client_name
WHEN Clients.Client_central = 'False' AND Clients.Client_assigned_to IS NULL THEN Clients.Client_name
ELSE Clients1.Client_name
END AS Central,

ROW_NUMBER() OVER(Partition by

CASE 
WHEN Clients.Client_central = 'True' THEN Clients.Client_name
WHEN Clients.Client_central = 'False' AND Clients.Client_assigned_to IS NULL THEN Clients.Client_name
ELSE Clients1.Client_name
END

) AS Row_Number

FROM
Clients
LEFT OUTER JOIN Clients Clients1 ON (Clients.Client_assigned_to = Clients1.OID)

Central	Row_Number
null	1
null	2
Client_1	1
Client_1	2
Client_1	3
Client_2	1
Client_2	2
Client_3	1