Assigning a value of 1 only to the lowest value for an address type for each customerID in a table

Database

Assigning a value of 1 only to the lowest value for an address type for each customerID in a table

Hi, I have a table with many customerIDs and each customerID can have many records each with a different address type. The address types are assigned values as follows: 1 = Home address, 2 = Postal address, 3 = work address, 4 = alternative home address. For each customerID in my table I want to assign as "Y" flag (or a value of 1) to a variable I will create (PRMY_ADDR_FLAG) (primary address flag) only to the record with the address corresponding to the lowest number in the list I just gave; all other records for that customerID should have a value of "N" or a 0 for their value of PRMY_ADDR_FLAG. How would I do this?

Thanks in advance, Peter

2 REPLIES
Senior Apprentice

Re: Assigning a value of 1 only to the lowest value for an address type for each customerID in a table

This should return the expected result:

case
when address_type
= min(address_type)
over (partition by customerID)
then 'Y'
else 'N'
end

Re: Assigning a value of 1 only to the lowest value for an address type for each customerID in a table

Thanks for the reply, Dieter!