sparqlwikidatawikidata-query-service

Selecting only the statement with the minimum date qualifier


I'm trying to write a query telling me the age upon which heads of state first took office. I'm having trouble isolating the position held statement with the earliest start time qualifier. I can't seem to put MIN() in the right place.

Here's a query returning all of the ages upon taking office (with more than one entry for those taking office more than once):

SELECT ?headOfState ?headOfStateLabel ?age ?countryLabel ?positionLabel 
{
 # find heads of state positions
 hint:Query hint:optimizer "None".
 ?position wdt:P279* wd:Q48352 .

 # sovereign states only
 ?position wdt:P1001 ?country .
 ?country wdt:P31 wd:Q3624078 .
  
 # fetch names of officeholders
 ?headOfState wdt:P39 ?position .

 # birthdates of officeholders
 ?headOfState wdt:P569 ?dob. hint:Prior hint:rangeSafe true. 
  
 # date of term start
 ?headOfState p:P39 ?statement .
 ?statement ps:P39 ?position . 
 ?statement pq:P580 ?termStart. hint:Prior hint:rangeSafe true.
 FILTER("1950-01-01"^^xsd:dateTime <= ?termStart && ?termStart < "1980-01-01"^^xsd:dateTime)
 BIND(YEAR(?termStart)-YEAR(?dob) as ?age)
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
ORDER BY DESC(?age)

link to wdqs

Earlier, before this update to my question, I was using SELECT DISTINCT and misplacing MIN() like so: BIND(MIN(YEAR(?termStart))-YEAR(?dob) as ?age), producing odd errors: it returns Anwar Sadat becoming president of Egypt at age 32. Sadat's date of birth is 1918, and there is no position held with a start time of 1950. So where does the number 32 come from?

Now, thanks to help I received, I have the right ages, but how do I select only the minimum age?

If, in the first line, I SELECT (MIN(?age) as ?ageMin), I get a bad aggregate (which makes sense I guess, because there are multiple entries for each head of state and this doesn't pick the single correct entry).

I've tried subqueries like

  {SELECT (MIN(?termStart) as ?termStartMin) WHERE {
 ?headOfState p:P39 ?statement .
 ?statement ps:P39 ?position . 
 ?statement pq:P580 ?termStart. hint:Prior hint:rangeSafe true.
 FILTER("1950-01-01"^^xsd:dateTime <= ?termStart && ?termStart < "1980-01-01"^^xsd:dateTime)}}
 BIND(YEAR(?termStartMin)-YEAR(?dob) as ?age)

but these are giving me crazy results.


Solution

  • After moving MIN() from the body of the query to SELECT (thanks to the answer and comments I received), I had to sort out my aggregates. This discussion of bad aggregates helped, and the query now does what I want it to do:

    # Age of heads of state upon taking power, 1950-1980
    SELECT DISTINCT ?headOfState ?headOfStateLabel (MIN(?age) as ?ageMin) ?countryLabel ?positionLabel
    {
     # find heads of state positions
     hint:Query hint:optimizer "None".
     ?position wdt:P279* wd:Q48352 .
    
     # sovereign states only
     ?position wdt:P1001 ?country .
     ?country wdt:P31 wd:Q3624078 .
      
     # fetch names of officeholders
     ?headOfState wdt:P39 ?position .
    
     # birthdates of officeholders
     ?headOfState wdt:P569 ?dob. hint:Prior hint:rangeSafe true. 
      
     # date of term start
     ?headOfState p:P39 ?statement .
     ?statement ps:P39 ?position . 
     ?statement pq:P580 ?termStart. hint:Prior hint:rangeSafe true.
      
     # 1950-1980 term start only
     FILTER("1950-01-01"^^xsd:dateTime <= ?termStart && ?termStart < "1980-01-01"^^xsd:dateTime)
     
     # calcuate age 
     BIND(YEAR(?termStart)-YEAR(?dob) as ?age)
     SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
    }
    GROUP BY ?headOfState ?headOfStateLabel ?countryLabel ?positionLabel
    ORDER BY DESC(?ageMin)
    

    try it