免责声明：

当使用多个表时，请使用表名限定列。您还没有这样做，因此我们不知道aread_code驻留在两个表中的哪个表中。在我的回答中，我假设它是客户的区域。如果不是，则需要不同的回答。

答复：

按customer_id和区号分组，这样每个客户占一行，并且需要orders表中的最大产品/项目ID（我认为它们是从同一序列中提取的，因此可以使用此ID继续进行）。
最简单的方法是在子查询中获取最大ID，可以直接在select子句中获取，也可以在from子句中获取。
下面是在SELECT子句中执行此操作的方法：

select
  c.*,
  (
    select greatest(max(productid), max(itemid))
    from orders o
    where o.custid = c.custid
  ) as max_id
from customer c 
where c.custtype = 'EXECUTIVE';

下面是在FROM子句中执行此操作的一种方法：

select
  c.*,
  agg.max_id
from customer c 
outer apply
(
  select greatest(max(productid), max(itemid)) as max_id
  from orders o
  where o.custid = c.custid
) agg
where c.custtype = 'EXECUTIVE';

下面是在FROM子句中执行此操作的另一种方法：

select
  c.*,
  agg.max_id
from customer c 
left outer join
(
  select
    custid,
    greatest(max(productid), max(itemid)) as max_id
  from orders
  group by custid
) agg on agg.custid = c.custid
where c.custtype = 'EXECUTIVE';

如果你只想要至少有一个订单的客户，那么我推荐使用FROM子句的方法，你必须把OUTER APPLY转换成CROSS APPLY，或者把LEFT OUTER JOIN转换成INNER JOIN。

您的代码中有几个错误。主要的混淆是没有为列使用表别名前缀。错误地使用了一个组，并且您的表名ORDER有问题-如果它是表的名称。ORDER是Oracle中的保留字，如果它是表的名称，则您应该使用类似“YOUR_OWNER_NAME”.“ORDER”...的内容。下面是带有一些示例数据和结果的更正代码：

WITH 
    customers  (CUSTID, PRODUCTID, AREAD_CODE, CUSTOMER_NAME, LOCATION, GENDER, ADDRESS, CUSTTYPE)  AS
        (
            Select 1, 1, 63,  'Name 1', 'Location 1', 'M', 'Address 1', 'EXECUTIVE' From Dual Union All
            Select 2, 1, 63,  'Name 1', 'Location 1', 'M', 'Address 1', 'EXECUTIVE' From Dual Union All
            Select 3, 3, 63,  'Name 1', 'Location 1', 'M', 'Address 1', 'EXECUTIVE' From Dual Union All
            Select 4, 7, 63,  'Name 1', 'Location 1', 'M', 'Address 1', 'EXECUTIVE' From Dual           
        ),
    orders (ORDER_ID, CUSTID, ITEMID, SOME_COLUMN)  AS
        (
            Select 1, 1, 1, 'Some other data' From Dual Union All
            Select 2, 2, 1, 'Some other data' From Dual Union All
            Select 3, 3, 1, 'Some other data' From Dual Union All
            Select 4, 3, 3, 'Some other data' From Dual Union All
            Select 5, 4, 1, 'Some other data' From Dual Union All
            Select 6, 4, 8, 'Some other data' From Dual 
        )

select 
    c.custid, c.aread_code, GREATEST(MAX(c.productid), MAX(o.itemid)) "MAX_ID"
from   
    CUSTOMERS C 
inner join 
    ORDERS O ON c.custid = o.custid
where  
    c.custtype = 'EXECUTIVE'
group by
    c.custid, c.aread_code

    CUSTID AREAD_CODE     MAX_ID
---------- ---------- ----------
         1         63          1 
         4         63          8 
         3         63          3 
         2         63          1

根据您的实际数据，您可以使用部分或全部选项来获取其余列。
选项1 -按照Beefstu以下评论中的建议进行选择和分组

select Distinct
    c.custid, c.customer_name, c.location, c.address, c.gender, c. custtype, c.aread_code, 
    GREATEST(MAX(c.productid), MAX(o.itemid)) "MAX_ID"
from   
    CUSTOMERS C 
inner join 
    ORDERS O ON c.custid = o.custid
where  
    c.custtype = 'EXECUTIVE'
group by
    c.custid, c.customer_name, c.location, c.address, c.gender, c. custtype, c.aread_code
order  by c.custid

    CUSTID CUSTOMER_NAME LOCATION   ADDRESS   GENDER CUSTTYPE  AREAD_CODE     MAX_ID
---------- ------------- ---------- --------- ------ --------- ---------- ----------
         1 Name 1        Location 1 Address 1 M      EXECUTIVE         63          1 
         2 Name 1        Location 1 Address 1 M      EXECUTIVE         63          1 
         3 Name 1        Location 1 Address 1 M      EXECUTIVE         63          3 
         4 Name 1        Location 1 Address 1 M      EXECUTIVE         63          8

选项2. -将分析函数MAX（）OVER（）与Distinct关键字一起使用（对于大数据集，可能会导致性能成本过高）-结果与上面相同

select Distinct
    c.custid, c.customer_name, c.location, c.address, c.gender, c. custtype, c.aread_code, 
    GREATEST(MAX(c.productid) OVER(Partition By c.custid), MAX(o.itemid) OVER(Partition By c.custid)) "MAX_ID"
from   
    CUSTOMERS C 
inner join 
    ORDERS O ON c.custid = o.custid
where  
    c.custtype = 'EXECUTIVE'
order  by c.custid

选项3 -对子查询使用左连接-请参阅ThorstenKettner提供的解决方案