QGIS: Select feature using SQL with highest value for attribute, grouped by another attribute

Question

I have a relatively small attribute table that includes a column where data (parameter 1) is identical across several rows. I would like to select only the row with the highest parameter 2 based on grouping by parameter 1.

I have no clue if this is possible as I am relatively inexperienced but I have tried using the "if" function to select but I am unable to create anything that searches across the whole attribute table rather than across a single row.

I have included an annotated image of my attribute table to help clarify.

Answer 1

This could be done with GROUP BY but with ranking you can have more flexibility on your query.

SELECT
    "A".*
FROM "Paired Data Sr-90" AS "A"
JOIN (
    SELECT
        "Sample_No",
        ROW_NUMBER() OVER (
            PARTITION BY "Lat"
            ORDER BY "Lab_Result" DESC
        ) AS "rank"
    FROM "Paired Data Sr-90"
) AS "B"
USING("Sample_No")
WHERE "B"."rank" <= 1 -- you can limit the number of results per partition here.

I know it sounds stupid to do a self join, but when you use a window function such as ROW_NUMBER() you can't use the WHERE statement. So you need to use the "ranking query" as a inner query. To load data to QGIS from a query you need to perform the query in the table not in an inner query, so there comes the self join.

Note: I'm supposing that the field Sample_No is unique if not create an unique field and change the names in the query.

Answer 2

You can use QGIS expressions. To search across the whole attribute table (not just one several row), use aggregate functions. With array_agg() you can get values from one field, grouped by another field.

Use select by expression with this expression:

parameter2 = array_max (array_agg (parameter2, group_by:=parameter1))

By the way: QGIS expressions are not SQL, but they use function-names derived from different languages like SQL, Python, PostGIS etc.

Blue rows selected: highest value for parameter2 inside each group with same value for parameter1: