QGIS - SQL GROUP BY and MAX

Question

I have a layer which contains a unique ID (UID) for each feature and another column of Scores.

For example:

UID, Score
A12,1 
A13,1
A14,4
A12,2
A13,3
A14,5

I would like to use an SQL statement to group the data by their UID value and return the highest score.

For example, using the table above, A12 would equal 2, A13 = 3 and A14 = 5.

There appears to be several locations where I could complete this SQL statement?

Which is the correct Query Builder to use and how do you complete this query in SQL?

Vincent Bré · Accepted Answer · 2019-12-24 12:37:37Z

4

Open the DB Manager > Virtual Layers > SQL Window and execute the following request.

SELECT UID, max(Score) as max_score
FROM test -- name of your layer
GROUP BY UID

answered Dec 24, 2019 at 12:37

Vincent Bré

3,8555 silver badges22 bronze badges

I get the following error? Query execution error on CREATE TEMP VIEW _tview AS SELECT UID, max(Score) as max_Score FROM my_layer GROUP BY UID: 1 - near "Order": syntax error
– RobN
Dec 24, 2019 at 12:56
"SELECT UID, max(Score) as max_Score FROM my_layer GROUP BY UID" Is this really your SQL code? This code does not match your error message!
– Mike
Dec 25, 2019 at 18:55
@BobJim There is no error with my request. Retry !
– Vincent Bré
Dec 27, 2019 at 7:06

Add a comment |

1 Answer 1