1

I need to be able to select my entire table but where there are duplicate id's, only select 1 of them based on the data in a different field.

For example if my table looks like this

enter image description here

I want to select all rows, but if there are 2 of the same id, select only the row with Billing as the address type.

Improve this question
2
  • Is it ok to assume that Billing will be there only for ids which are duplicates? Commented Oct 27, 2017 at 13:59
  • Just an idea. Where clause should contain 2 conditions. First one checks that there is only one row with specific ID. It can be done by executing a subquery with count. The second condition checks if address type is billing. OR operation should be used. Commented Oct 27, 2017 at 14:05

2 Answers 2

Reset to default
2

You can do it this way:

select * from Table1 
where (AddressType='Billing') or
(AddressType='Shipping' and ID not in (select ID from Table1 where AddressType='Billing'))
order by ID

Explanation:

1st condition is to filter only Billing address types.

2nd condition is to filter Shipping address types which do not have Billing with the same ID.

Result in SQL Fiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This should work assuming Billing and Shipping are the only address types and there's never more than 2 rows with the same Id
@jussius: Based on the data sample, this will work. Also, ID seems to be the only field used to specify the relation between those records.
This only returns rows with billing type. I need it to return shipping if there isnt a duplicate row with billing
@Sam: This will return all billing types and shipping if there is no billing type for that ID. Did you check the query in Fiddle link?
I think this has worked, I was just prioritizing the wrong one. Once I swapped it around to prioritize shipping over billing it worked
0

Try this - 

SELECT *, ADDRESS
FROM (SELECT MIN(ID), ADDRESSTYPE
      FROM YOUR_TABLE
      GROUP BY ADDRESS) X
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.