All Forums Database
kudentov 2 posts Joined 07/16
17 Jul 2016
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

dnoeth 4628 posts Joined 11/04
17 Jul 2016

This should return the expected result:

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

 

Dieter

kudentov 2 posts Joined 07/16
18 Jul 2016

Thanks for the reply, Dieter!

You must sign in to leave a comment.