SQL Choosing one address per person based on preference if they live in a certain state, otherwise t-CodePudding

In my address table I have: Unique_ID, Address_Type, Street, City, State, Zip, Effective_Date

Each Unique_ID can have multiple address types (which are 1L, 1P, AP, PO and AR).

I want to choose just one address per Unique_ID.

If the person has an address located in UT, I want to choose among all address types where UT is the associated State the one with the most recent Effective_Date.

Otherwise if none of the addresses are in UT, then I want the record with the most recent Effective_Date.

I thought about using CASE but it just returns a row for every address the person has when I need one row per person.

I have an Oracle table using SQL Plus.

CodePudding user response：

I'm not sure if Oracle and SQL plus use windowed functions, but this is how I'd do it in SQL Server:

SELECT 
     Unique_ID, 
     Address_Type, 
     Street, 
     City, 
     State, 
     Zip, 
     Effective_Date
FROM    (   SELECT  
                *,
                DENSE_RANK() OVER(PARTIITON BY Unique_ID ORDER BY Priority, Effective_Date DESC) [Rank]
            FROM    (   SELECT  
                            *, 
                            CASE State WHEN 'UT' THEN 1 ELSE 2 END AS [Priority]
                        FROM    Address
                    ) [PrioritizedAddresses]
        ) RankedAdresses
WHERE   [Rank] = 1