Removing duplicate features based on a condition on another attribute column in QGIS

Question

I want to remove duplicate features (from a polygons shapefile) based on a condition on another attribute column in QGIS. For example, if I have the following attribute table of a polygon shapefile:

Column_A  | Column_B

1         a

2         a

1         b

2         b

I want to keep only one feature for each group of duplicates (or triplicates, etc) of column B, by keeping the feature corresponding to the smallest column A value for each duplicate group.

The result would be:

Column_A  | Column_B

1         a

1         b

Answer 1

Variant 1: select features to keep

Use Select by expression with this expression (screenshot 1):

column_a = minimum (column_a, group_by:=column_b)

Then export/save only the selected features and you're done. You get a new layer, the original data remains unchanged. All attributes for the minimum value of column A are retained (your request in a comment to the solution by @Pigreco - see screenshot 2)

Or: You could also Invert selection (click the icon, see screenshot 1) and delete the duplicates. All attributes for the minimum value of column A are retained (your request in a comment to the solution by @Pigreco - see screenshot 2).

---

Variant 2: select features to delete

An even faster way (no need to invert selection): directly select the duplicates you want to delete with one of these modifications of the expression. Then delete the selected features. No new layer is created, the initial data is changed, duplicates are irrevocably deleted:

• replace = by <>:

  column_a <> minimum (column_a, group_by:=column_b)

• replace = by !=:

  column_a = minimum (column_a, group_by:=column_b) - tribute to @pigreco

• add not in front of the expression (screenshot 2):

  not column_a = minimum (column_a, group_by:=column_b)

screenshot 1:

screenshot 2, including column_c that is retained - selected lines (blue) can be deleted, red ones will be kept:

Answer 2

You can achieve this using a virtual layer.

The output is a dynamic layer, which you can choose to export if you want to persist the results.

go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the query

select min(column_a),column_b, column_c, *
from  a
group by a.column_B

You can choose to list the columns to be returned, or to use min(column_a), * to get them all.

It will return 1 row by columnB value. Since we are asking for the smallest columnA, the other field values will be for the row containing the smallest columnA

Answer 3

1. Order by expression with the expression "Field_A"||"Field_B"

This algorithm sorts a vector layer according to an expression. Be careful, it might not work as expected with some providers, the order might not be kept every time.

2. Delete duplicates by attribute:

Removes duplicate rows by a field value (or multiple field values). The first matching row will be retained, and duplicates will be discarded. Optionally, these duplicate records can be saved to a separate output for analysis.

Answer 4

From Processing Tools, search for aggregate algorithm:

1. select the layer of interest;
2. select the field to aggregate for duplicates;
3. select minimum value;
4. select first value

EDIT:

in case there was a third column Column_C (see message below)

the Column_C is calculated by expression:(>= QGIS 3.18)

with_variable(
    'cucu',
    array_agg( "Column_A" , "Column_B" ),
array_agg( "Column_C" , "Column_B" )[array_find(@cucu,array_min(@cucu))])

example data: https://gist.githubusercontent.com/pigreco/0ad8205396093557dad8b2d3864a6ddf/raw/039187175547c62d206b200ba15879eb3714b22f/duplicate.csv