Selecting some distinct and some not-distinct tags in SPARQL

Question

I'm trying to query DBPedia for a list of properties relating to a given class in the ontology, but since the human-readable "labels" aren't always clear, I'd also like to provide an example from the database. The problem is that while I want to select all distinct properties, I only want a single example of each property. Here's how my query looks without capturing the example:

SELECT DISTINCT ?prop ?title WHERE {
    ?thing ?prop [].
    ?thing a <http://dbpedia.org/ontology/Currency>.
    ?prop rdf:type rdf:Property.
    ?prop rdfs:label ?title.
} ORDER BY DESC(COUNT(DISTINCT ?thing))
LIMIT 100

If I change it in this way, I start getting duplicate values for ?prop:

SELECT DISTINCT ?prop ?title ?example WHERE {
    ?thing ?prop ?example.
    ?thing a <http://dbpedia.org/ontology/Currency>.
    ?prop rdf:type rdf:Property.
    ?prop rdfs:label ?title.
} ORDER BY DESC(COUNT(DISTINCT ?thing))
LIMIT 100

I'm very new to using SPARQL and database queries in general, so it's not at all clear to me how to do this. Ideally, I'd have something like DISTINCT(?prop) ?title ?example, which selects every unique value for prop, and returns its title and an example.

Remittent

Answer 1

In your second queries the distinct applies to the combination of values of ?prop ?title and ?example. Therefore you're not getting any duplicates, for instance for the following two rows obtained in the second query:

dbpedia2:subunitName    "subunit name "@en  "cent"@en
dbpedia2:subunitName    "subunit name "@en  "centavo"@en

they aren't duplicates because the third row ?example has two different values "cent"@en and "centavo"@en

One posible way to solve this is to use GROUP BY and MIN to get just the lowest ranked value for ?label and ?example, i.e:

SELECT ?prop MIN(?title) MIN(?example) WHERE {
    ?thing ?prop ?example.
    ?thing a <http://dbpedia.org/ontology/Currency>.
    ?prop rdf:type rdf:Property.
    ?prop rdfs:label ?title.
} GROUP BY ?prop

Cable

This works beautifully, but is the GROUP BY argument necessary? I've implemented it with ORDER BY DESC(COUNT(DISTINCT ?thing)) and it seems to work still. Is there a circumstance where ORDER BY will give some unpleasant results that group by won't? – Remittent

@Remittent To be a proper valid query you cannot ORDER BY an aggregate directly - this is not permitted by the SPARQL 1.1 specification. This is presumably a Virtuoso extension and will make the query non-portable i.e. it won't necessarily work on non-Virtuoso endpoints. Please see my answer for alternative query forms which should do the same thing and be portable – Dorthadorthea

Answer 2

Here is an alternative way to achieve what you want with subqueries:

SELECT ?prop ?title ?example 
WHERE 
{
    ?thing a <http://dbpedia.org/ontology/Currency>.
    ?prop rdf:type rdf:Property.
    { SELECT ?title ?example WHERE { ?thing ?prop ?example . ?prop rdfs:label ?title. } LIMIT 1 }
}
LIMIT 100

This has the advantage that it is SPARQL 1.1 standards compliant, as I stated in my comment ordering by an aggregate is not permitted by the standard so you are using a vendor specific extension which will limit the portability of your query.

If you do want to order by an aggregated value in a way that is portable across SPARQL 1.1 implementations then you must first project it like so:

SELECT ?s (COUNT(?p) AS ?predicates) WHERE
{
  ?s ?p ?o
} GROUP BY ?s ORDER BY DESC(?predicates)

Dorthadorthea

Answer 3

If you don't care about the example but you care about speed, SAMPLE can be much faster than GROUP BY

SELECT ?prop (SAMPLE(?title) AS ?title) (SAMPLE(?example) AS ?example) 
WHERE {
    ?thing ?prop ?example.
    ?thing a <http://dbpedia.org/ontology/Currency>.
    ?prop rdf:type rdf:Property.
    ?prop rdfs:label ?title.
} LIMIT 100

You probably won't notice the difference on dbpedia since it caches query results, but I noticed a huge difference when using other endpoints.

I ran into the same issue op had while creating an autocomplete service that queries multiple sparql endpoints. I needed to find a single link related to a search term, of which the link itself wasn't very important, but the speed of the query was very important.

Virgiliovirgin