How to use GREATEST function with Over Partition by in Oracle-CodePudding

In the below code I want to select customer_name, location, gender and address along with customerid, aread_code.

select 
    customerid, aread_code, GREATEST(MAX(productid), MAX(itemid))
from   
    CUSTOMER C 
inner join 
    ORDER O ON c.custid = o.custid
where  
    c.custtype = 'EXECUTIVE'
group 
    customerid, by aread_code;

I tried GREATEST function along with OVER PARTITION BY to display required columns. It's throwing an error.

Could you please help me to select the required columns.

Thank you.

CodePudding user response：

DISCLAIMER:

When working with more than one table, qualify the columns with their table name. You haven't done so, so we don't know what of the two tables the aread_code resides in. In my answer here I assume it is the customer's area. If it isn't then you need a different answer.

ANSWER:

You group by customer_id and area code. This gives you one row per customer. And you want the maximum product/item ID from the orders table. (I suppose they are drawn from the same sequence, so you can use this ID somehow to go on from there.)

The easiest approach for this is to get the maximum ID in a subquery. Either directly in the select clause or in the from clause.

Here is how to do this in the SELECT clause:

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';

Here is one way to do this in the FROM clause:

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';

And here is another way to do this in the FROM clause:

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';

If you only want customers with at least one order, then I recommend the approach with the FROM clause. You'd have to turn the OUTER APPLY into a CROSS APPLY resp. the LEFT OUTER JOIN into an INNER JOIN for this.

CodePudding user response：

There are several mistakes in your code. The main confusion is not using table alias prefix for columns. There is a group by mistake and a problem with your table name ORDER - if it is a name of a table. ORDER is a reserved word in Oracle and if it is the name of the table then you should use something like "YOUR_OWNER_NAME"."ORDER".... Here is the corected code with some sample data and result:

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

There are different options to get the rest of the columns depending on your actual data you could use some or all of them.

Option 1 - select and group by as suggested in Beefstu's comment below

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

Option 2. - using analytic functions MAX() OVER() with Distinct keyword (could be performance costly with big datasets) - result is the same as above

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

Option 3 - using left join to a subquery - see the solution offered by Thorsten Kettner