How to get max(column_name) from a table irrespective of a where clause in the query?

Question

I have a requirement of inserting data into a table and one of the columns is seq_number. I have a where clause in the select query and I want to insert the max(seq_num)+1 for every record that I want to insert. The max of seq_num is giving me the max for the where clause and not the actual max from the table.

INSERT INTO TABLE_NAME 
SELECT
    NEWID(),
    MAX(SEQ_ORD_R)+1,  -- This should be the max from table irrespective of the where clause
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM TABLE_NAME
WHERE SOFA_K = 'FD5B6BE8-F1CF-42C0-9216-B13163413F96'

Answer 1

SELECT MAX(SEQ_ORD_R) FROM TABLE_NAME INTO @K;
INSERT INTO TABLE_NAME 
SELECT
    NEWID(),
    (SELECT @K := @K+1),
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM TABLE_NAME
WHERE SOFA_K = 'FD5B6BE8-F1CF-42C0-9216-B13163413F96'

Answer 2

INSERT INTO TABLE_NAME 
SELECT
    NEWID(),
    max_seq_r+row_number() over (partition by 1 order by max_seq_r),  -- This should be the max from table irrespective of the where clause
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM TABLE_NAME A inner join 
(select max(SEQ_ORD_R) as max_seq_r from TABLE_NAME) B on 1=1
WHERE SOFA_K = 'xyz'