SPARQL group by and order by: not ordered

Question

I follow up on query where the schema.org database is used to find the number of children of a class - as a simpler database than my application. I want to get the names of the children concatenated in alphabetic order. The query:

prefix schema:  <http://schema.org/>
prefix rdfs:    <http://www.w3.org/2000/01/rdf-schema#>

select    ?child  (group_concat (?string) as ?strings) 
where {
  ?child  rdfs:subClassOf schema:Event .
     ?grandchild rdfs:subClassOf ?child .
  bind (strafter(str(?grandchild), "http://schema.org/") as ?string)
}   group by ?child  order by asc(?string)
limit 20

gives

schema:PublicationEvent   "OnDemandEvent BroadcastEvent"
schema:UserInteraction    "UserPageVisits UserComments UserPlays UserBlocks UserDownloads UserPlusOnes UserLikes UserCheckins UserTweets"

Which is not alphabetically ordered. If I replace the sort order to desc the result is exactly the same. I seem not to understand how group by, order by and possibly bind interact.

score 4 · Answer 1 · answered Nov 23 '17 at 19:06

4

An additional select subquery is required to push the order inside the groups:

prefix schema:  <http://schema.org/>
prefix rdfs:    <http://www.w3.org/2000/01/rdf-schema#>

select    ?child  (group_concat (?string) as ?strings) 

where {
    select * 
    {
     ?child  rdfs:subClassOf schema:Event .
     ?grandchild rdfs:subClassOf ?child .
     bind (strafter(str(?grandchild), "http://schema.org/") as ?string)
    } order by asc(?string)
}   group by ?child  
limit 20

answered Nov 23 '17 at 19:06

user855443

2,596
3
25
37

Stanislav Kralin is right. There is no guarantee that the order will be kept because `group_concat` works on multisets. – UninformedUser Nov 23 '17 at 20:02
1

I use jena-fuseki 3.4; does in your example the order not change when you replace `asc` by `desc`? – user855443 Nov 29 '17 at 04:34
in order to get it to work as the asker wants, the selects have to be flipped. `group by` must be the inner one and then `order by`. `group by` ignores the previous ordering – notsodev May 28 '20 at 20:41

Stanislav Kralin · Answer 2 · 2018-01-22T22:45:31.090

2

18.5.1.7 GroupConcat:

The order of the strings is not specified.

From the horse's mouth:

On 2011-04-22, at 19:01, Steve Harris wrote:

On 2011-04-22, at 06:18, Jeen Broekstra wrote:

However, looking at the SPARQL 1.1 query spec, I think this is not a guaranteed result: as far as I can tell the solution modifier ORDER BY should be applied to the solution sequence after grouping and aggregation, so it can not influence the order of the input for the GROUP_CONCAT.

That's correct.

edited Jan 22 '18 at 22:45

answered Nov 23 '17 at 17:36

Stanislav Kralin

11,070
4
35
58

The order of the strings in `group_concat` is not specified but taken from the sequence of the clauses and thus it should be defined by the `order by` clause. I found later, that an additional `select` subquery is required. – user855443 Nov 23 '17 at 19:03
1

@user855443 That is not true. Stanislav Kralin is totally right. The input of `group_concat` is a multiset, thus, order will be lost. Depending on the triple store it might be retained, but according to the W3C specs there is no guarantee for it. – UninformedUser Nov 23 '17 at 20:01
1

@StanislavKralin So seems we will have to wait till SPARQL 1.2? – Median Hilal Sep 29 '19 at 18:44

SPARQL group by and order by: not ordered

2 Answers2

Linked

Related