SQL query to get certain matching rows

user_18137723 0
Viewed 84

I was asked to write a query to return address for some customers in our system.

Below is a sample data structure for the table.

The goal is to display the results for customers with address-type = W and if no then to display the address-type = H. I have tried the the where clause with or clause, case clause and in my attempts I get the both records with W and H address-type.

customerid address-type record-count address1 address2 address3 address4 city state zip
123 W 1 123 james ave Apt 1 City1 AA 11000
123 H 2 134 james ave Apt 2 City2 AB 23000
124 W 1 145 john ave Apt 3 City3 AA 32001
124 H 2 155 john ave Apt 4 City4 AB 12001
125 W 1 1000 wilson ave Apt 5 City5 AA 12009
126 H 1 11 smith ave Apt 6 City6 AB 14001
126 H 2 50 third ave Apt 7 City7 AA 15001
postgresql sql
1 Answers

You can use ROW_NUMBER() to order the rows according to your specific logic and then pick the first one only for each customer.

For example:

select *
from (
  select *,
    row_number() over(partition by customerid order by address_type desc) as rn
  from t
) x
where rn = 1
user_64361910
Related