Select columns maximum and minimum value for all records

Question

I have a table as below; I want to get the column names having maximum and minimum value except population column (ofcourse it will have maximum value) for all records.

State   Population   age_below_18  age_18_to_50   age_50_above
 1         1000          250          600            150
 2         4200          400          300            3500

Result :

   State    Population   Maximum_group    Minimum_group    Max_value   Min_value
     1         1000      age_18_to_50      age_50_above      600         150
     2         4200      age_50_above      age_18_to_50     3500         300

Answer 1

Another option: use a combination of UNPIVOT(), which "rotates columns into rows" (see: documentation) and analytic functions, which "compute an aggregate value based on a group of rows" (documentation here) eg

Test data

select * from T ;

STATE  POPULATION  YOUNGERTHAN18  BETWEEN18AND50  OVER50  
1      1000        250            600             150     
2      4200        400            300             3500 

UNPIVOT

select *
from T
unpivot  (  
  quantity for agegroup in (
    youngerthan18  as 'youngest'
  , between18and50 as 'middleaged'
  , over50         as 'oldest'
  )
);

-- result
STATE  POPULATION  AGEGROUP    QUANTITY  
1      1000        youngest    250       
1      1000        middleaged  600       
1      1000        oldest      150       
2      4200        youngest    400       
2      4200        middleaged  300       
2      4200        oldest      3500 

Include Analytic Functions

select distinct
  state
, population
, max( quantity ) over ( partition by state ) maxq
, min( quantity ) over ( partition by state ) minq
, first_value ( agegroup ) over ( partition by state order by quantity desc ) biggest_group
, first_value ( agegroup ) over ( partition by state order by quantity ) smallest_group
from T
  unpivot  (  
    quantity for agegroup in (
      youngerthan18  as 'youngest'
    , between18and50 as 'middleaged'
    , over50         as 'oldest'
    )
  )
;

-- result
STATE  POPULATION  MAXQ  MINQ  BIGGEST_GROUP  SMALLEST_GROUP  
1      1000        600   150   middleaged     oldest          
2      4200        3500  300   oldest         middleaged

Example tested w/ Oracle 11g (see dbfiddle) and Oracle 12c.

Caution: {1} column (headings) need adjusting (according to your requirements). {2} If there are NULLs in your original table, you should adjust the query eg by using NVL().

An advantage of the described approach is: the code will remain rather clear, even if more 'categories' are used. Eg when working with 11 age groups, the query may look something like ...

select distinct
  state
, population
, max( quantity ) over ( partition by state ) maxq
, min( quantity ) over ( partition by state ) minq
, first_value ( agegroup ) over ( partition by state order by quantity desc ) biggest_group
, first_value ( agegroup ) over ( partition by state order by quantity ) smallest_group
from T
  unpivot  (  
    quantity for agegroup in (
      y10     as 'youngerthan10'   
    , b10_20  as 'between10and20'
    , b20_30  as 'between20and30'
    , b30_40  as 'between30and40'
    , b40_50  as 'between40and50'
    , b50_60  as 'between50and60'
    , b60_70  as 'between60and70'
    , b70_80  as 'between70and80'
    , b80_90  as 'between80and90'
    , b90_100 as 'between90and100'
    , o100    as 'over100'
    )
  )
order by state
;

See dbfiddle.

Answer 2

with CTE as (
    select T.* 
        --step2: rank value
        ,RANK() OVER (PARTITION BY "State", "Population" order by "value") "rk" 
    from (
        --step1: union merge three column to on column
        select 
         "State", "Population", 
         'age_below_18' as GroupName,
         "age_below_18" as "value"
        from TestTable
        union all
        select 
         "State", "Population",     
         'age_18_to_50' as GroupName,
         "age_18_to_50" as "value"
        from TestTable
        union all
        select 
         "State", "Population",     
         'age_50_above' as GroupName,
         "age_50_above" as "value"
        from TestTable   
    ) T
)
select T1."State", T1."Population"
    ,T3.GroupName Maximum_group
    ,T4.GroupName Minimum_group    
    ,T3."value" Max_value   
    ,T4."value" Min_value

--step3: max rank get maxvalue,min rank get minvalue   
from (select "State", "Population",max( "rk") as Max_rank from CTE group by "State", "Population") T1
left join (select "State", "Population",min( "rk") as Min_rank from CTE group by "State", "Population") T2 
    on T1."State" = T2."State" and T1."Population" = T2."Population"
left join CTE T3 on T3."State" = T1."State" and T3."Population" = T1."Population" and T1.Max_rank = T3."rk"
left join CTE T4 on T4."State" = T2."State" and T4."Population" = T2."Population" and T2.Min_rank = T4."rk"

---

SQL Fiddle DEMO LINK

Hope it help you :)

Answer 3

Assuming none of the values are NULL, you can use greatest() and least():

select state, population,
       (case when age_below_18 = greatest(age_below_18, age_18_to_50, age_50_above)
             then 'age_below_18'
             when age_below_18 = greatest(age_below_18, age_18_to_50, age_50_above)
             then 'age_18_to_50'
             when age_below_18 = greatest(age_below_18, age_18_to_50, age_50_above)
             then 'age_50_above'
         end) as maximum_group,
       (case when age_below_18 = least(age_below_18, age_18_to_50, age_50_above)
             then 'age_below_18'
             when age_below_18 = least(age_below_18, age_18_to_50, age_50_above)
             then 'age_18_to_50'
             when age_below_18 = least(age_below_18, age_18_to_50, age_50_above)
             then 'age_50_above'
         end) as minimum_group,
        greatest(age_below_18, age_18_to_50, age_50_above) as maximum_value,
        least(age_below_18, age_18_to_50, age_50_above) as minimum_value
from t;

If your result set is actually being generated by a query, there is likely a better approach.

An alternative method "unpivots" the data and then reaggregates:

select state, population,
       max(which) over (dense_rank first_value order by val desc) as maximum_group,
       max(which) over (dense_rank first_value order by val asc) as minimum_group,
       max(val) as maximum_value,
       min(val) as minimum_value
from ((select state, population, 'age_below_18' as which, age_below_18 as val
       from t
      ) union all
      (select state, population, 'age_18_to_50' as which, age_18_to_50 as val
       from t
      ) union all
      (select state, population, 'age_50_above' as which, age_50_above as val
       from t
      )
     ) t
group by state, population;

This approach would have less performance than the first, although it is perhaps easier to implement as the number of values increases. However, Oracle 12C supports lateral joins, where a similar approach would have competitive performance.