2

I thought my problem was solved with the question I asked bevor (QGIS 3.4 feature filter maximum() does not work). But there is still a problem. I still want to filter a layer with the maximum value of field_a, grouped by field_b. I used this filter: "field_a" IN SELECT MAX("field_a") FROM 'layer_test' GROUP BY "field_b") The result is a reduced number of features, but there are still several features from one value in field_b with different values in field_a. Here a picture from the wrong result:

This is the wrong result

But I want only the maximum value of field_a, grouped by field_b. For example I want for value '12' in field_b only the feature with value '6' in field_a. If I use the expression without GROUP BY, then it works like I expected, I get only the total maximum value. How can I get the right GROUP BY result? Unfortunately I don't know SQL...

1

If I understand well, you want to get for each value of field_a the record having the biggest value of field_b. The linked answer does find the biggest value of field_b but returns all rows from field_a having this value.

You can update the data filter to filter (layer properties/source/provider feature filter) on both values. Note the extra parentheses around the two field names.

I have tested it on a Postgres layer, I don't know if it will work with other data providers.

("field_a","field_b") 
  in (select "field_a",
             max("field_b") 
     from layer_test 
     group by "field_a")
| improve this answer | |
  • Are u referring to my answer ? the aim is not to find the biggest value of field_b but to find the biggest value of field_a using the field_b grouping ...i just re-checked the filtering and it seems ok to me (qgis 3.4.8, on a shapefile at least) ... – snaileater Nov 4 '19 at 19:30
  • no, sorry, to the linked URL. I will adjust the answer – JGH Nov 4 '19 at 19:36
  • Thank you very much, now I get the result wich I want! I was closed to it, but the details I could not figure out by myself. – Nane Nov 4 '19 at 20:03
  • i supposed u both switched field_a and field_b or ... i don't understand anything to the original question ... – snaileater Nov 4 '19 at 21:00
  • Yes, you are right - I switched the field_a and field_b. I forgot to mention it... – Nane Nov 4 '19 at 21:17
1

My german is not good but your setting (in your screen capture) is on "show all features" so you won't see the result of your filtering ...

I wasn't able to use JGH answer in that part of QGIS interface but Kazuhito's answer is working pretty fine for me and did find the right maximum values ...

So ... to summarize the following formula :

"field_a"  =  maximum("field_a", group_by:= "field_b")

in "advanced filter" did work for me (and did filter the displayed lines of the table) ...

If you think your SQL formula did work at that place i would be happy to hear it but obviously u didn't get the expected result ...

| improve this answer | |
  • Values filtered at the data provider level (layer properties/source/provider feature filter) will not reach the attribute table. It is as if these records don't exist – JGH Nov 4 '19 at 19:21
  • I don' want to filter in the attribute table, I want to have the filter in the layer properties. The answer from JGH solves my problem! – Nane Nov 4 '19 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.