2

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?

Improve this question
3
  • 1
    SELECT from the table with ORDER BY area, species in a sub-query, then GROUP_CONCAT in the outer. A consuming statement will keep that order if nothing changes it in betwen - you may need to DISTINCT area, species (or GROUP BY area, species) in the sub-query.
    – geozelot
    Jan 17, 2022 at 20:43
  • 1
    You cannot use ORDER BY within a subquery.
    – EndorCartographer
    Jan 18, 2022 at 7:14
  • 3
    You can. The arbitrariness of order is a design choice, and until now SQLite follows the order of presentment, meaning that a consuming statement will keep the order of the providing statement. PostgreSQL and other DBS don't necessarily follow that design, but they, unlike SQLite, usually support inline ORDER BY. If still in doubt, this QA has both this method, and an explicitly deterministic alternative with a good explanation.
    – geozelot
    Jan 18, 2022 at 8:22

2 Answers 2

Reset to default
2

An alternation using processing tools and expressions (I know it's OT, but it might be useful)

Aggregate algorithm, set as below:

enter image description here

  1. your layer
  2. field to group
  3. expression to use

expression used:

concatenate_unique( 
     group_by:="area",
     concatenator:=',',
     expression:="species",
     order_by:="species")

output:

enter image description here

Improve this answer
1
  • 1
    I like your approch and it seems a simple way to change the concatenator if one prefers not to use a comma. But in my case a pure SQL solution is what I want.
    – EndorCartographer
    Jan 19, 2022 at 17:53
0

I have always thought that you cannot use ORDER BY in subqueries. This query solves my issue:

SELECT 
  area, group_concat(DISTINCT species)
FROM (
  SELECT 
     area, species
  FROM mytable
  ORDER BY species ASC
  )
GROUP BY area
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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