CODEWITHSUNDEEP
mysql
user7070541
user7070541

Reputation:

MySQL select 1 of multiple rows with same field based on where clause

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.

Upvotes: 1

Views: 1018

Answers (2)

Raging Bull
Raging Bull

Reputation: 18767

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

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

Try this - 

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

Upvotes: 0

Related Questions