I have a sub query that gets the address for the person filing a complaint. The person can have multiple addresses. We used to just select the address of type 'OR' which makes for a clean subquery of...

```
(SELECT TOP 1 Address 
 FROM Address WHERE NameID = PersNameID AND AddressTypeCode = 'OR') AS ComplainantAddress

```

Now, the way the addresses are entered, they may or may not have an address of type 'OR'. They may only have an address of type 'ML' or of type 'BU' and 'ML'.

I need to get the first address that is populated for the person, looking in a specified order (by type). I could do a huge CASE statement but is that the best way? Or should I change to a JOIN with an ORDER By and take the first non null value?

Looking for suggestions.