Geek Answers Handbook

Teradata CASE and HOWING COUNT

I have a Handset table. There are several duplicate imei, and I have to choose only one imei each with these requirements:

  • when unique imei is found, select this
  • if imei duplicate found, if one data_capable = 'Y', select it.
  • when imei duplicate is found, if both data_capable = 'Y', select one with max (revenue)
  • when imei duplicate is found if both data_capable = 'N' then select one with max (income)
 IMEI MSISDN REVENUE DATA_CAPABLE 35622200000001 4282336700001 1000 Y 35622200000001 4282336700002 2000 N 35622200000002 4282336700003 3000 Y 35622200000003 4282336700004 4000 Y 35622200000004 4282336700005 5000 Y 35622200000005 4282336700006 6000 Y 35622200000005 4282336700007 7000 Y 35622200000006 4282336700008 8000 Y 35622200000007 4282336700009 9000 N 35622200000007 4282336700010 1100 N

I am confused to combine CASE WHEN and HAVING COUNT(*)>1 for this case. Any help from the master is really appreciated.

+5
source share
1 answer

This is an example for ROW_NUMBER.

Assuming the data_capable parameters are Y and N :

 select * from tab qualify row_number() over (partition by imei -- for each imei order by data_capable desc -- 'Y' first ,revenue desc -- max(revenue) first ) = 1
+1
source

More articles:


All Articles