CODEWITHSUNDEEP
sqlms-access
Sencon
Sencon

Reputation: 1

only eliminate certain duplicates in data in MS Access SQL

I'm using MS Access 2013, trying to remove certain duplicates from a set of data. I need to prioritize the data where "field 5" is "2" however if there isn't a duplicate or a "2" I need to keep the data where the field is "1".

Current State Data

|Field1 |Field 2| Field 3| Field 4 |Field 5|
|AAA    | BBB   | CCC   | DDD      |1      |
|AAA    | BBB   | CCC   | DDD      |2      |
|III    | JJJ   | KKK   | LLL      |2      |
|XXX    | YYY   | ZZZ   | TTT      |1      |
|MMM    | NNN   | OOO   | PPP      |1      |
|MMM    | NNN   | OOO   | PPP      |1      |

Desired State

|Field1 |Field 2 |Field 3 |Field 4 |Field 5|
|AAA    |BBB     |CCC     | DDD    |2      |
|III    |JJJ     |KKK     | LLL    |2      |
|XXX    |YYY     |ZZZ     | TTT    |1      |
|MMM    |NNN     |OOO     | PPP    |1      |

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

In this case, I think the simplest method is conditional aggregation. For your particular data, this works:

select field1, field2, field3, field4,
       max(field5) as field5
from t
group by field1, field2, field3, field4;

This is not necessarily a general solution to the problem you describe. But it appears to be what you want to accomplish with your data. For instance, this assumes that field5 only takes on the values 1 and 2, as in the sample data.

Upvotes: 1

Related Questions