How to get maximum column values across a row in Teradata sql?

Question

I have a table named cnst_chrctrstc_abc with 10 columns (equ_gender1 - bb_population_flag) each row which contain numeric values (count) . I want to get maximum 5 values out of each row across those 10 numeric columns.

The query I have looks something like the following ..

SEL 
FROM 
(
SEL 
SUM(CASE WHEN COALESCE(act.equ_gender1,'') = COALESCE(inact.equ_gender1,'') THEN 0 ELSE 1 END ) AS equ_gender1_chg_cnt,
SUM(CASE WHEN COALESCE(act.exp_ex_bmyr1,'') = COALESCE(inact.exp_ex_bmyr1,'') THEN 0 ELSE 1 END ) AS exp_ex_bmyr1_chg_cnt,
SUM(CASE WHEN COALESCE(act.equ_age1,'') = COALESCE(inact.equ_age1,'') THEN 0 ELSE 1 END ) AS equ_age1_chg_cnt,
SUM(CASE WHEN COALESCE(act.maritalstatus1,'') = COALESCE(inact.maritalstatus1,'') THEN 0 ELSE 1 END ) AS maritalstatus1_chg_cnt,
SUM(CASE WHEN COALESCE(act.person_type1,'') = COALESCE(inact.person_type1,'') THEN 0 ELSE 1 END ) AS person_type1_chg_cnt,
SUM(CASE WHEN COALESCE(act.homeowner,'') = COALESCE(inact.homeowner,'') THEN 0 ELSE 1 END ) AS homeowner_chg_cnt,
SUM(CASE WHEN COALESCE(act.dwelling_size,'') = COALESCE(inact.dwelling_size,'') THEN 0 ELSE 1 END ) AS dwelling_size_chg_cnt,
SUM(CASE WHEN COALESCE(act.lengthofresidence,'') = COALESCE(inact.lengthofresidence,'') THEN 0 ELSE 1 END ) AS lengthofresidence_chg_cnt,
SUM(CASE WHEN COALESCE(act.childrenage0_18,'') = COALESCE(inact.childrenage0_18,'') THEN 0 ELSE 1 END ) AS childrenage0_18_chg_cnt,
SUM(CASE WHEN COALESCE(act.bb_population_flag,'') = COALESCE(inact.bb_population_flag,'') THEN 0 ELSE 1 END ) AS bb_population_flag


FROM
(SEL * FROM arc_mdm_Tbls.cnst_chrctrstc_abc WHERE load_id=1024 AND cnst_chrctrstc_end_dt='9999-12-31' (DATE))act
LEFT JOIN
(SEL * FROM arc_mdm_Tbls.cnst_chrctrstc_abc WHERE load_id=1024 AND cnst_chrctrstc_end_dt<'9999-12-31' (DATE) 
QUALIFY ROW_NUMBER() OVER (PARTITION BY cnst_mstr_id ORDER BY cnst_chrctrstc_strt_ts DESC)=1
)inact
ON act.cnst_mstr_id = inact.cnst_mstr_id
)X

I know SEL GREATEST would produce the maximum value out of each row . But I want 5 top values and assign a rank to them.

Something like for some row first five columns may hold the top 5 values and for some last five i.e. homeowner to bb_population_flag may hold the top 5 values.

so if the columns and values from cnst_chrctrstc_abc look something like the following

cdi_batch_id | a | b | c | d | e | f | g | h | i |j
1024         |116|105|102|100|117|119|108|104|101|121

so the select query should return me columns j,f,e,a,g having the top 5 values. And then I would assign a rank to them accordingly .

Should it be done using unpivot or something ? Thanks in advance.

Answer 1

Yes, you need to unpivot your result.

Before TD14.10 you will need a list of those column names, either as a table

create table ColumnList (col varchar(128));
Insert into ColumnList('equ_gender1'       );
Insert into ColumnList('exp_ex_bmyr1'      );
Insert into ColumnList('equ_age1'          );
Insert into ColumnList('maritalstatus1'    );
Insert into ColumnList('person_type1'      );
Insert into ColumnList('homeowner'         );
Insert into ColumnList('dwelling_size'     );
Insert into ColumnList('lengthofresidence' );
Insert into ColumnList('childrenage0_18'   );
Insert into ColumnList('bb_population_flag');

or on-thy-fly using a bulky

with ColumnList as 
 (
   select * from (select 'equ_gender1'        as Col) as dt union all
   select * from (select 'exp_ex_bmyr1'       as Col) as dt union all
   select * from (select 'equ_age1'           as Col) as dt union all
   select * from (select 'maritalstatus1'     as Col) as dt union all
   select * from (select 'person_type1'       as Col) as dt union all
   select * from (select 'homeowner'          as Col) as dt union all
   select * from (select 'dwelling_size'      as Col) as dt union all
   select * from (select 'lengthofresidence'  as Col) as dt union all
   select * from (select 'childrenage0_18'    as Col) as dt union all
   select * from (select 'bb_population_flag' as Col) as dt
 )

Then you CROSS JOIN to unpivot:

select
   col,
   case col 
      when 'equ_gender1'        then equ_gender1       
      when 'exp_ex_bmyr1'       then exp_ex_bmyr1      
      when 'equ_age1'           then equ_age1          
      when 'maritalstatus1'     then maritalstatus1    
      when 'person_type1'       then person_type1      
      when 'homeowner'          then homeowner         
      when 'dwelling_size'      then dwelling_size     
      when 'lengthofresidence'  then lengthofresidence 
      when 'childrenage0_18'    then childrenage0_18   
      when 'bb_population_flag' then bb_population_flag
   end as Counts,
   rank() over (order by Counts desc) as rnk
FROM
 (
   your current select
 ) as dt
cross join ColumnList
qualify rnk <= 5

In TD14.10 you could utilize the TD_UNPIVOT function:

SELECT Col, rank() over (order by Counts desc) as rnk 
from TD_UNPIVOT(
        ON (
             your current select
           )
        USING
           VALUE_COLUMNS('Counts')
           UNPIVOT_COLUMN('Col')
           COLUMN_LIST('equ_gender1'
                      ,'exp_ex_bmyr1'
                      ,'equ_age1'          
                      ,'maritalstatus1'
                      ,'person_type1'
                      ,'homeowner'
                      ,'dwelling_size'
                      ,'lengthofresidence'
                      ,'childrenage0_18'
                      ,'bb_population_flag')

        ) dt
qualify rnk <= 5;

Edit:

Additionally you might replace your LEFT JOIN with a single OLAP-function. Depending on the number of rows per cnst_mstr_id this might be more efficient as you need a ROW_NUMBER anyway:

SEL 
SUM(CASE WHEN COALESCE(equ_gender1,'') = COALESCE(last_equ_gender1,'') THEN 0 ELSE 1 END ) AS equ_gender1_chg_cnt,
...
FROM
 ( SELECT 
      min(equ_gender1) OVER (PARTITION BY cnst_mstr_id ORDER BY cnst_chrctrstc_strt_ts DESC rows between 1 following and 1 following) as equ_gender1,
      ...
   FROM arc_mdm_Tbls.cnst_chrctrstc_abc 
   WHERE load_id=1024
   qualify cnst_chrctrstc_end_dt= date '9999-12-31'
 )act