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)
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.
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)