Group by a column and build a single result row based on condition

here is simplest way & probably most performant solution:

select accounts
       ,case when cnt > 1 then 1 else order_number end order_number
       ,status,state  
from (
   select * 
      , row_number() over (partition by account order by case when order_number = 4 then 1 else 0 end desc) rn 
      , count(*) over (partition by account) cnt  
) t 
where rn = 1 

You can do it this way:

Create dummy table for testing

[Create table test1(accounts varchar(10), order_number int, status varchar(10),state varchar(10))
  insert into test1 values('1111',4,'ok','full')
  insert into test1 values( '2256'  ,         4  ,      'ok'  ,    'full')
  insert into test1 values('3344' ,          1  ,      NULL  ,  NULL)
  insert into test1 values('1111' ,          1  ,      NULL  ,  NULL)
  insert into test1 values('8743' ,          4  ,      'ok'  ,    'full')
  insert into test1 values('2256'  ,         1  ,      NULL  ,  NULL)][1]

Query, no hard coded values

Select accounts,
order_number,
status,
state 
from (
        select row_number() over(partition by t1.accounts order by t1.order_number desc) rnum, 
        t1.accounts,
        isnull(t2.order_number,t1.order_number) order_number ,
        t1.status,
        t1.state
        from test1 t1
        left join (select * from test1 where order_number=1) t2 on t1.accounts = t2.accounts and t1.order_number <> t2.order_number
    ) a
where rnum = 1

Result set

accounts   order_number status     state
---------- ------------ ---------- ----------
1111       1            ok         full
2256       1            ok         full
3344       1            NULL       NULL
8743       4            ok         full

UPDATE: Adding Test Result Column

Select accounts,
order_number,
status,
state,
[TEST RESULTS]
from (
        select row_number() over(partition by t1.accounts order by t1.order_number desc) rnum, 
        t1.accounts,
        isnull(t2.order_number,t1.order_number) order_number ,
        t1.status,
        t1.state,
        case
        when isnull(t2.order_number,t1.order_number) = '1' then 'pass'
        when isnull(t2.order_number,t1.order_number) = '4' then 'fail'
        end as 'TEST RESULTS'
        from test1 t1
        left join (select * from test1 where order_number=1) t2 on t1.accounts = t2.accounts and t1.order_number <> t2.order_number
    ) a
where rnum = 1

Just another option using WITH TIES in concert with the window functions min() over() and row_number() over()

Example

Select top 1 with ties 
       account
      ,order_number = min(order_number) over(partition by account)
      ,status
      ,state
 From  myTable
 Order By row_number() over (partition by account order by order_number desc)

Results

account order_number    status  state
1111    1               ok      full
2256    1               ok      full
3344    1               NULL    NULL
8743    4               ok      full

I only have Access to work with. Output accomplished with:

Query1:

SELECT Q1.account, Q1.order_number, Q2.status, Q2.state
FROM (SELECT DISTINCT account, order_number FROM myTable WHERE order_number = 1)  AS Q1 
INNER JOIN (SELECT DISTINCT account, status, state FROM myTable WHERE order_number=4) AS Q2 
ON Q1.account = q2.account;

Query2:

SELECT account, order_number, status, state FROM Query1
UNION SELECT account, order_number, status, state FROM myTable WHERE NOT account IN(SELECT account FROM Query1);

This query has the desired result, but I have doubts about the WHERE NOT EXISTS part, because I don't know what is the meaning of order_number and, if your real problem is more complex than your question, it may become complicated.

Just changed ok to 1 and full to 1

SELECT  [T1].[account], [T1].[order_number], COALESCE([T2].[status], [T1].[status]) AS [status], COALESCE([T2].[state], [T1].[state]) AS [state]
FROM    [dbo].[myTable]         [T1]
    LEFT JOIN [dbo].[myTable]   [T2]
        ON [T2].[account] = [T1].[account]
        AND [T2].[order_number] = 4
WHERE NOT EXISTS (
    SELECT  1
    FROM    [dbo].[myTable] [T3]
    WHERE [T3].[account] = [T1].[account]
        AND [T1].[order_number] = 4
        AND [T3].[order_number] = 1
);

If order_number is always 1 and 4 then below could be the most optimized solution for your problem. Here first I have put a account wise sequence number for all rows starting from 1 in descending order of order_number. So If there is one row for any account number then it 's sequence number (rn) will be 1 and if there are more than one rows then row with order_number 4 will have the sequence number (rn) 1.

So we got our row to select. And to replace order_number 4 with 1 if it's not the only row we calculated row count for each account as column cnt. If cnt>1 and order_number is 4 then we replaced order_number with 1 using case when.

Schema and insert statements:

 create table myTable(account   int, order_number int, status varchar(10), state varchar(10));
 insert into myTable values(1111,           4,        'ok',      'full');
 insert into myTable values(2256,           4,        'ok',      'full');
 insert into myTable values(3344,           1,        NULL,    NULL);
 insert into myTable values(1111,           1,        NULL,    NULL);
 insert into myTable values(8743,           4,        'ok',      'full');
 insert into myTable values(2256,           1,        NULL,    NULL);

Query:

 with cte as 
 (
   select account,order_number,status,state,row_number()over(partition by account order by order_number desc)rn,
   count(order_number)over(partition by account )cnt
   from mytable
 )select account,(case when order_number=4 and cnt>1 then 1 else order_number end) order_number,status,state 
 from cte where rn=1

Output:

db<>fiddle here

You can achieve the above result by using Aggregate Function Like MIN & MAX

DECLARE  @myTable TABLE (account   int, order_number int, status varchar(10), state varchar(10)) 
 INSERT INTO @myTable VALUES(1111,           4,        'ok',      'full');
 INSERT INTO @myTable VALUES(2256,           4,        'ok',      'full');
 INSERT INTO @myTable VALUES(3344,           1,        NULL,    NULL);
 INSERT INTO @myTable VALUES(1111,           1,        NULL,    NULL);
 INSERT INTO @myTable VALUES(8743,           4,        'ok',      'full');
 INSERT INTO @myTable VALUES(2256,           1,        NULL,    NULL);

Query:

SELECT  account,MIN(order_number) order_number,MAX(status) status,MAX(State) State
FROM @myTable
GROUP BY account