There are two areas S1 and S2. Every area was visited two times to collect bumblebees. I want all species that has been found on each area as a comma separated string in alphabetical order.
area| date |species
--------------------------------
S1 |2021-04-01|B.terrestris
S1 |2021-04-01|B.cryptarum
S1 |2021-04-01|B.lapidarius
S1 |2021-06-01|B.terrestris
S1 |2021-06-01|B.lapidarius
S1 |2021-06-01|B.terrestris
S1 |2021-06-01|B.cryptarum
S1 |2021-06-01|B.lapidarius
S2 |2021-04-01|B.lapidarius
S2 |2021-06-01|B.lapidarius
S2 |2021-06-01|B.cryptarum
S2 |2021-06-01|B.cryptarum
S1|B.cryptarum,B.lapidarius,B.terrestris
S2|B.cryptarum,B.lapidarus
Why does a query like
SELECT
area, group_concat(DISTINCT species ORDER BY species ASC)
FROM table
GROUP BY area
not work in QGIS3? I receive a Syntax Error. When I remove ORDER BY
I get the expected result except the alphabetical order. Why is that?
SELECT
from the table withORDER BY area, species
in a sub-query, thenGROUP_CONCAT
in the outer. A consuming statement will keep that order if nothing changes it in betwen - you may need toDISTINCT area, species
(orGROUP BY area, species
) in the sub-query.ORDER BY
within a subquery.ORDER BY
. If still in doubt, this QA has both this method, and an explicitly deterministic alternative with a good explanation.