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.
Looks like data did'nt copied properly
SELECT * FROM tab
OVER (PARTITION BY ENT_ID
ORDER BY CASE WHEN PRODUCT_TYPE = 'GHP' THEN 0 ELSE 1 END,
CURRENT_BAL DESC) = 1