5

I have a table Handset. There are some duplicate imei's and i have to select only one imei each with these requirement:

  1. when unique imei found then pick that one
  2. when duplicate imei found, if one data_capable = 'Y', pick that one.
  3. when duplicate imei found, if both data_capable = 'Y', pick one with max(revenue)
  4. when duplicate imei found, if both data_capable = 'N' then pick one with max(revenue)
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 master really appreciated

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
mdrahadian
  • 53
  • 4

1 Answers1

1

This is a case for a ROW_NUMBER.

Assuming that the options for data_capable are Yand 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
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • It works like a charm! Really big thank you @dnoeth, you are the man!! I didn't even know row_number() exist :D RESULT: imei msisdn revenue data_capable 35622200000001 4282336700001 1,000 Y 35622200000002 4282336700003 3,000 Y 35622200000003 4282336700004 4,000 Y 35622200000004 4282336700005 5,000 Y 35622200000005 4282336700007 7,000 Y 35622200000006 4282336700008 8,000 Y 35622200000007 4282336700009 9,000 N – mdrahadian Jun 18 '15 at 07:26