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



  • 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


  • 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)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2