I'm trying to come up with a query that will give me one Address row from a table based on two criteria. Each customer can have one or more Address rows.
My first choice is the oldest address row that's in a specific state (top 1 row for customer ordered by AddressID) for NY.
My second choice is that if the customer doesn't have an address in NY I want the oldest AddressID from the list of addresses for that customer (no state criteria).
Not all customers have any offices in NY state. But if they have one, I want the oldest address that's in NY. If they don't have any offices in NY, give me the address with the lowest AddressID. If they have some addresses that are in NY and some that aren't, I want the oldest NY address, but only one row for each customer.
I've been tinkering with a UNION of two select statements with RANK() = 1 (one = NY, one <> NY) but can't figure out how to come up with just the one row without resorting to a stored procedure and a couple INSERT statements (second statement could exclude the customers already in the temp table, etc.).
If anyone knows how to do this, I would enjoy seeing your ideas.
You can use window functions with a a conditional order by clause. Assuming the following data structure:
addresses
address_id
customer_id
state
You would phase the query as:
select *
from (
select a.*,
row_number() over(
partition by customer_id
order by case when state = 'NY' then 1 else 2 end, address_id
) rn
from addresses
) a
where rn = 1