Is there any method like max() function? oracle version 11.1.0

Question

I want to select the maximum value, but if the user has more than one value in one column, then leave all

I have a table :

create table package (U_id, service, offer,product) as
 select   1, 12345, null, null  from dual union all
select   1, null, 25468, null  from dual union all
select   1, null, null, 23456  from dual union all
select   2, 12345, 58889, null  from dual union all
select   2, 43456, null, null  from dual ;

I tried

select u_id, max(service), max(offer),max(product) 
from package
group by u_id

but, in the case of the second user, took only the maximum number from the service.

I want to get such a table:

u_id	service	offer	product
1	12345	25468	23456
2	123456	58889
2	43456

Answer 1

I want to select the maximum value, but if the user has more than one value in one column, then leave all

I'm reading this as

I want to select the maximum value for each row if there is only one value for each column for that user UNION ALL select all rows for the other users

There is no CASE statement for data sets (eg CTE). You'll have to limit each part with a where/having clause and UNION ALL them together.

with data (U_id, service, offer,product) as (
    -- original data as a CTE instead of a table
    select   1, 12345, null, null  from dual union all
    select   1, null, 25468, null  from dual union all
    select   1, null, null, 23456  from dual union all
    select   2, 12345, 58889, null  from dual union all
    select   2, 43456, null, null  from dual )
,single_row as (
    -- defines the first part requested (1 value per column)
    select U_id, max(service) service
        ,max(offer) offer
        ,max(product) product
    from data
    group by u_id
    having count(service) = 1 and count(offer) = 1 and count(product) = 1
), multi_row as (
    -- defines the 2nd part requested (all others)
    select u_id, service, offer, product
    from data a
    where not exists (select null
                      from single_row b
                      where a.u_id=b.u_id)
)
select u_id, service, offer, product from single_row
union all
select u_id, service, offer, product from multi_row;

(note: you didn't specify how you wanted ordered)