only eliminate certain duplicates in data in MS Access SQL

Question

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      |

This may be a duplicate of some question, but the suggested question does not appear to be an appropriate duplicate. — Gordon Linoff, Jan 30 '18 at 00:03

score 1 · Answer 1 · answered Jan 29 '18 at 23:58

1

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.

answered Jan 29 '18 at 23:58

Gordon Linoff

1,242,037
58
646
786

1

And why would an answer that appears to correctly address the OP's question be downvoted? Anonymously? – Gordon Linoff Jan 30 '18 at 00:02
Good question. +1. – Gustav Jan 30 '18 at 07:19

only eliminate certain duplicates in data in MS Access SQL

1 Answers1