0

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Gordon Prince
  • 142
  • 2
  • 9
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/q/6841605/2029983) If not, why not? – Thom A Nov 12 '20 at 18:47

1 Answers1

1

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
GMB
  • 216,147
  • 25
  • 84
  • 135