Qualify solution

Database
Enthusiast

Qualify solution

Hi Experts,

 

I have a situation where i have columns ENT_ID , ACCOUNT_ID , PRODUCT_TYPE and CURRENT_BAl.

1 ENT_ID can have multiple ACCOUNT_ID and 1 ACCOUNT_ID  will have one PRODUCT_TYPE.

I have to select 1 account per Ent_Id but first preference should be PRODUCT_TYPE ='GHP' with max current_bal if not then any other account with max CURRENT_BAL. In my result set one ENT_CUST_ID should have only one row.

Scenario:-







Input      
ENT_ID ACCOUNT_ID CURRENT_BAL PRODUCT_TYPE
12345 11111 100000 GHP
12345 22222 2000000 GHP
12345 333333 50000 XYZ
987654 65872 50000 DFG
987654 54321 600000 ABC
34567 90909 10000 GHP
34567 80808 130000 RST
       
Expected Result    
       
ENT_ID ACCOUNT_ID CURRENT_BAL PRODUCT_TYPE
12345 22222 2000000 GHP
987654 54321 600000 ABC
34567 90909 10000 GHP

Please help.

4 REPLIES
Enthusiast

Re: Qualify solution

Looks like data did'nt copied properly

Input

ENT_ID              ACCOUNT_ID               CURRENT_BAL            PRODUCT_TYPE

12345                   11111                        100000                          GHP

12345                   22222                        2000000                        GHP

12345                   333333                      50000                            XYZ

987654                 65872                        50000                            DFG

987654                  54321                       600000                          ABC

34567                    90909                       10000                            GHP

34567                    80808                       130000                          RST

Expected Result

ENT_ID                  ACCOUNT_ID                CURRENT_BAL             PRODUCT_TYPE

12345                      22222                          2000000                        GHP

987654                    54321                          600000                          ABC

34567                      90909                         10000                             GHP

Enthusiast

Re: Qualify solution

Experts please help :(

Senior Apprentice

Re: Qualify solution

SELECT * FROM tab
QUALIFY
ROW_NUMBER()
OVER (PARTITION BY ENT_ID
ORDER BY CASE WHEN PRODUCT_TYPE = 'GHP' THEN 0 ELSE 1 END,
CURRENT_BAL DESC) = 1
Enthusiast

Re: Qualify solution

Thank you so much Dieter . Really appreciate your help!!