To all of you SQL guru's,
I'm having a mental block. I want to update a memory table, in this case phone1. I want to update it based on a specific rank. If the record has type 1 then use it. If not, use type 8, then finally if type 1 and 8 are not there, use type 5
I'm using a CTE to get what I need. Here is what I'm working with:
;WITH derived AS(
select customer_no,phone,[type] ,rank() OVER (PARTITION BY phone ORDER BY
CASE [type]
WHEN 1 THEN 1
WHEN 8 THEN 2
WHEN 5 THEN 3
END) ranked
from T_phone
)
select *
from derived
order by customer_no
Any ideas?
Tony,
I think you want to PARTITION BY customer_no, not phone. This will rank the phone types for each customer.
David