单表MAX(SEQUENCE)查询正常，关联表后查询异常技术咨询

Hey there, let's break down why your MAX(B.SEQUENCE) isn't returning the expected maximum value when joining tables—it's all about how the GROUP BY clause is working here.

The Root Problem #

In your original joined query, you included B.SEQUENCE in the GROUP BY clause. When you do that, the database splits your results into groups for every unique value of B.SEQUENCE (along with the other grouped fields). That means the MAX(B.SEQUENCE) for each group is just the sequence value of that group itself—not the overall maximum sequence number for your target DOCNUMBER='NUMBER'.

Solutions Based on Your Exact Need #

Scenario 1: Get the global max sequence + related details

If you want the single highest SEQUENCE value for DOCNUMBER='NUMBER', plus the corresponding Country, Company, UserID, etc., you need to first find that max sequence, then join back to your tables to pull in the associated data:

SELECT 
    max_seq_tbl.MAX_SEQ,
    A.COUNTRY,
    A.COMPANY,
    A.DOCNUMBER,
    B.USERID,
    B.REASON
FROM TABLE1 A
INNER JOIN TABLE2 B ON A.DOCNUMBER = B.DOCNUMBER
-- First, get the maximum sequence for your target DOCNUMBER
INNER JOIN (
    SELECT DOCNUMBER, MAX(SEQUENCE) AS MAX_SEQ
    FROM TABLE2
    WHERE DOCNUMBER = 'NUMBER'
    GROUP BY DOCNUMBER
) max_seq_tbl ON B.DOCNUMBER = max_seq_tbl.DOCNUMBER 
              AND B.SEQUENCE = max_seq_tbl.MAX_SEQ
WHERE A.DOCNUMBER = 'NUMBER'

Scenario 2: Get max sequence per grouped set of fields

If your goal is to calculate the highest sequence for each unique combination of A.COUNTRY, A.COMPANY, A.DOCNUMBER, B.USERID, B.REASON, you just need to remove B.SEQUENCE from your GROUP BY clause. This way, the database groups by those other fields and computes the max sequence within each group:

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 A.COUNTRY, A.COMPANY, A.DOCNUMBER, B.USERID, B.REASON

Quick Recap #

• Including B.SEQUENCE in GROUP BY forces the database to create a group for every individual sequence value, making MAX() useless for getting an overall maximum.
• Choose the solution above that matches what you actually need to retrieve from your query!

内容的提问来源于stack exchange，提问作者Uriel Nicolas