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

i have table named cnst_chrctrstc_abc 10 columns (equ_gender1 - bb_population_flag) each row contain numeric values (count) . want maximum 5 values out of each row across 10 numeric columns.

the query have looks following ..

sel   ( sel  sum(case when coalesce(act.equ_gender1,'') = coalesce(inact.equ_gender1,'') 0 else 1 end ) equ_gender1_chg_cnt, sum(case when coalesce(act.exp_ex_bmyr1,'') = coalesce(inact.exp_ex_bmyr1,'') 0 else 1 end ) exp_ex_bmyr1_chg_cnt, sum(case when coalesce(act.equ_age1,'') = coalesce(inact.equ_age1,'') 0 else 1 end ) equ_age1_chg_cnt, sum(case when coalesce(act.maritalstatus1,'') = coalesce(inact.maritalstatus1,'') 0 else 1 end ) maritalstatus1_chg_cnt, sum(case when coalesce(act.person_type1,'') = coalesce(inact.person_type1,'') 0 else 1 end ) person_type1_chg_cnt, sum(case when coalesce(act.homeowner,'') = coalesce(inact.homeowner,'') 0 else 1 end ) homeowner_chg_cnt, sum(case when coalesce(act.dwelling_size,'') = coalesce(inact.dwelling_size,'') 0 else 1 end ) dwelling_size_chg_cnt, sum(case when coalesce(act.lengthofresidence,'') = coalesce(inact.lengthofresidence,'') 0 else 1 end ) lengthofresidence_chg_cnt, sum(case when coalesce(act.childrenage0_18,'') = coalesce(inact.childrenage0_18,'') 0 else 1 end ) childrenage0_18_chg_cnt, sum(case when coalesce(act.bb_population_flag,'') = coalesce(inact.bb_population_flag,'') 0 else 1 end ) bb_population_flag   (sel * arc_mdm_tbls.cnst_chrctrstc_abc load_id=1024 , cnst_chrctrstc_end_dt='9999-12-31' (date))act left join (sel * arc_mdm_tbls.cnst_chrctrstc_abc load_id=1024 , cnst_chrctrstc_end_dt<'9999-12-31' (date)  qualify row_number() on (partition cnst_mstr_id order cnst_chrctrstc_strt_ts desc)=1 )inact on act.cnst_mstr_id = inact.cnst_mstr_id )x 

i know sel greatest produce maximum value out of each row . want 5 top values , assign rank them.

something row first 5 columns may hold top 5 values , last 5 i.e. homeowner bb_population_flag may hold top 5 values.

so if columns , values cnst_chrctrstc_abc following

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

so select query should return me columns j,f,e,a,g having top 5 values. , assign rank them accordingly .

should done using unpivot or ? in advance.

yes, need unpivot result.

before td14.10 need list of column names, either table

create table columnlist (col varchar(128)); insert columnlist('equ_gender1'       ); insert columnlist('exp_ex_bmyr1'      ); insert columnlist('equ_age1'          ); insert columnlist('maritalstatus1'    ); insert columnlist('person_type1'      ); insert columnlist('homeowner'         ); insert columnlist('dwelling_size'     ); insert columnlist('lengthofresidence' ); insert columnlist('childrenage0_18'   ); insert columnlist('bb_population_flag'); 

or on-thy-fly using bulky

with columnlist   (    select * (select 'equ_gender1'        col) dt union    select * (select 'exp_ex_bmyr1'       col) dt union    select * (select 'equ_age1'           col) dt union    select * (select 'maritalstatus1'     col) dt union    select * (select 'person_type1'       col) dt union    select * (select 'homeowner'          col) dt union    select * (select 'dwelling_size'      col) dt union    select * (select 'lengthofresidence'  col) dt union    select * (select 'childrenage0_18'    col) dt union    select * (select 'bb_population_flag' col) dt  ) 

then cross join unpivot:

select    col,    case col        when 'equ_gender1'        equ_gender1              when 'exp_ex_bmyr1'       exp_ex_bmyr1             when 'equ_age1'           equ_age1                 when 'maritalstatus1'     maritalstatus1           when 'person_type1'       person_type1             when 'homeowner'          homeowner                when 'dwelling_size'      dwelling_size            when 'lengthofresidence'  lengthofresidence        when 'childrenage0_18'    childrenage0_18          when 'bb_population_flag' bb_population_flag    end counts,    rank() on (order counts desc) rnk  (    current select  ) dt cross join columnlist qualify rnk <= 5 

in td14.10 utilize td_unpivot function:

select col, rank() on (order counts desc) rnk  td_unpivot(         on (              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 might replace left join single olap-function. depending on number of rows per cnst_mstr_id might more efficient need row_number anyway:

sel  sum(case when coalesce(equ_gender1,'') = coalesce(last_equ_gender1,'') 0 else 1 end ) equ_gender1_chg_cnt, ...  ( select        min(equ_gender1) on (partition cnst_mstr_id order cnst_chrctrstc_strt_ts desc rows between 1 following , 1 following) equ_gender1,       ...    arc_mdm_tbls.cnst_chrctrstc_abc     load_id=1024    qualify cnst_chrctrstc_end_dt= date '9999-12-31'  )act