I have a table of customers and a table of service plans. The service plans have a hierarchy, like so (from greatest to least): Platinum > Diamond > Gold > (everything else).
A customer can have several service plans, but I want to select the customer name and the highest-level service plan that customer has. So far, I'm assigning a rank to each record based upon the hierarchy, and then doing a min or max on the rank column depending on how I assigned the rank.
SELECT customer_id, nam_service_plan
FROM customer a, service_plan b, cust_serv_xref c,
(SELECT customer_id, max(decode(nam_service_plan, 'PLATINUM', '1','DIAMOND','2','GOLD','3') as plan_rank
FROM customer aa, service_plan bb, cust_serv_xref cc
WHERE aa.customer_id = cc.customer_id
AND bb.plan_id = cc.plan_id) max_plan
a.customer_id = c.customer_id
AND b.plan_id = c.plan_id
AND a.customer_id = max_plan.customer_id
AND decode(nam_service_plan, 'PLATINUM', '1','DIAMOND','2','GOLD','3') = max_plan.max_rank
My question is, how can I group all the other plans into ranking 4? Is there some way to do the decode statement to say, "decode everything else"?
Or is there a better way to attack this problem?
Last edited by pie; 12-27-2005 at 03:56 PM.
Reason: CODE tags aren't working