• In the future, posting the sample data as a create table script with inserts for the sample data would help us out quite a bit (otherwise to test solutions we need to manually get all that data entered somewhere).

    Here's a good link on how to do that: https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    Anyway, perhaps something like this is what you're looking for?

    IF OBJECT_ID('tempdb.dbo.#companies') IS NOT NULL DROP TABLE #companies;

    CREATE TABLE #companies
    (
    CompanyName varchar(20),
    CompanyStatus varchar(20),
    CompanyNumber varchar(10),
    GroupNumber varchar(10)
    );

    INSERT INTO #companies
    VALUES
    ('Blackwell', 'group','A123G','A123'),
    ('Symphony','subCompany','s678','A123'),
    ('Blends','subCompany', 't123','A123'),
    ('Tiger', 'group','T123G','T123'),
    ('Pepper', 'group','Z987G','Z987'),
    ('Great Falls','subCompany','f545','T123');

    WITH  ordered_parent_companies AS
    (
    SELECT GroupNumber, rn=ROW_NUMBER() OVER (ORDER BY CompanyName)
    FROM  #companies
    WHERE  CompanyStatus='group'
    )

    SELECT c.*
    FROM  #companies c
       INNER JOIN
       ordered_parent_companies opc ON c.GroupNumber=opc.GroupNumber
    ORDER BY rn ASC,
          CompanyName ASC;

    Cheers!