Select Max(Field) not working

Question

I have the following issue:

SELECT MAX(SEQUENCE) FROM TABLE1
WHERE FIELD99= 'NUMBER'

That query is bringing the MAX value of Field1, and it's working perfectly. Now I want to join this information with another table to gather additional data, and I'm using this:

SELECT MAX(B.SEQUENCE), A.COUNTRY, A.COMPANY, 
A.DOCNUMBER, B.USERID, B.REASON
FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.DOCNUMBER=B.DOCNUMBER
WHERE A.DOCNUMBER='NUMBER'
GROUP BY B.SEQUENCE, A.COUNTRY, A.COMPANY, A.DOCNUMBER, B.USERID, B.REASON

I know the issue is the Grouping, but I can't figure out, I have tried several ways, I've noticed that when I removed all other fields it's bringing only the data I want.

If I run the first example given, I will get result: 007, which is the Max Value of Field SEQUENCE. If I run the other Query, I will get:

001 + all the other fields
002 + all the other fields
003 + all the other fields
004 + all the other fields
005 + all the other fields
006 + all the other fields
007 + all the other fields

I want the result to bring the same row as the first Query with the added data in Select

I was able to do this Query and solve the issue

SELECT DISTINCT A.REFFSEQU, A.CTRYCODE, A.COMPCODE, A.INVONUMB, A.REFFERRS, A.REFFERBY              
FROM TABLE1 A, TABLE2 B                                    
WHERE A.CTRYCODE=B.CTRYCODE AND A.COMPCODE=B.COMPCODE AND A.INVONUMB=B.INVONUMB
AND B.INVOCRDT > '20180101'                                                    
AND (A.REFFSEQU = ANY                                                          
 (SELECT MAX(REFFSEQU)                                                         
 FROM TABLE1 AS D                                                    
 WHERE D.CTRYCODE=A.CTRYCODE AND D.COMPCODE=A.COMPCODE                         
 AND D.INVONUMB= A.INVONUMB))                                                  
AND A.REFFERRS = 'DUPLICATION, RECORD REJEC'

This is the result, sorry not familiar on how to do a Table here

REFFSEQU    CTRYCODE    COMPCODE    INVONUMB    REFFERRS                       REFFERBY
001           631          01   number1         DUPLICATION, RECORD REJEC             id  
002           631          01   number2         DUPLICATION, RECORD REJEC             id
002           631          01   number3         DUPLICATION, RECORD REJEC             id
007           631          01   number4         DUPLICATION, RECORD REJEC             id
007           631          01   number5         DUPLICATION, RECORD REJEC             id

Answer 1

I would use a CTE to do this. It will simplify the grouping.

with tmp as (
  select docnumber, 
         max(sequence) as sequence,
         country,
         company
    from table1
    group by docnumber)
select a.sequence, a.country, a.company, a.docnumber, b.userid, b.reason
  from tmp a
    join table2 b on a.docnumber = b.docnumber
    where a.docnumber = 'NUMBER'

Note, I am using sequence from table1 rather than table2.

Edit: This is an initial guess based on the comments:

with tmp as (
  select docnumber, max(sequence) as sequence
    from table1
    group by docnumber),
tmp2 as (
  select distinct docnumber
    from table2
    where reason = 'Duplicated')
select a.sequence, a.country, a.company, a.docnumber, b.userid, b.reason
  from tmp a
    join table2 b on a.docnumber = b.docnumber
    join tmp2 c on a.docnumber = c.docnumber
    where a.docnumber = 'NUMBER'

You would not need the distinct in the tmp2 definition if no more than one sequence for each docnumber has a reason of 'Duplicated'.