How to project additional variables from SPARQL aggregates

Question

I would like to know the most recent population of Swiss municipalities with the help of Wikidata. To get the ones which have the preferred rank would be easy but not helpful. I have the following SPARQL query but now I have the problem to project the actual population count ?popWD for the latest date (and not only the date) out of the aggregation. As I understand, you cannot project values which are not aggregated.

SELECT ?wikiURI (max(?timeWDAll) AS ?timeWD) WHERE {            
      ?wikiURI wdt:P31 wd:Q70208;
               p:P1082 ?popWDStatement.
      ?popWDStatement pq:P585 ?timeWDAll;
                      ps:P1082 ?popWD.
}
GROUP BY ?wikiURI

I solved the problem with a subselect which does work but seems a little bit complicated and quite slow:

SELECT ?wikiURI ?timeWD ?popWD WHERE {
    ?wikiURI p:P1082 ?popWDStatement.
    ?popWDStatement ps:P1082 ?popWD;
                    pq:P585 ?timeWD.
    {
        SELECT ?wikiURI (max(?timeWDAll) AS ?timeWD) WHERE {
            ?wikiURI wdt:P31 wd:Q70208;
                     p:P1082 ?popWDStatement.
            ?popWDStatement pq:P585 ?timeWDAll.
        }
        GROUP BY ?wikiURI
    }
}

Are there better and faster solutions? The code snippets above should both work at: https://query.wikidata.org

Stanislav Kralin · Accepted Answer · 2017-07-23T09:00:51.900

It seems you can improve performance of your query by adding just one line:

SELECT ?wikiURI ?timeWD ?popWD WHERE {
    ?wikiURI wdt:P31 wd:Q70208.       #-- this line
    ?wikiURI p:P1082 ?popWDStatement. 
    ?popWDStatement ps:P1082 ?popWD;
                    pq:P585 ?timeWD.
    {
        SELECT ?wikiURI (max(?timeWDAll) AS ?timeWD) WHERE {
            ?wikiURI wdt:P31 wd:Q70208;
                     p:P1082 ?popWDStatement.
            ?popWDStatement pq:P585 ?timeWDAll.
        }
        GROUP BY ?wikiURI
    }
}

Take a look at these query execution plans (scroll down to the «Query evaluation statistics» section):

modified query (with ?wikiURI wdt:P31 wd:Q70208),
original query (without ?wikiURI wdt:P31 wd:Q70208).

Also, the variant below seems to be more readable and adaptable:

SELECT ?wikiURI ?timeWD ?popWD WHERE {
    ?wikiURI wdt:P31 wd:Q70208.
    ?wikiURI p:P1082 ?popWDStatement.
    ?popWDStatement ps:P1082 ?popWD;
                    pq:P585 ?timeWD.
    FILTER NOT EXISTS {
        ?wikiURI wdt:P31 wd:Q70208.       #-- this line is important for performance
        ?wikiURI p:P1082 ?popWDStatement_.
        ?popWDStatement_ ps:P1082 ?popWD_;
                         pq:P585 ?timeWD_.
        FILTER (?timeWD_ > ?timeWD)}
} ORDER BY DESC(?popWD)

Thank you for the second variant with the filter structure. This solution seems to be the same as the one presented [here](https://stackoverflow.com/questions/36181713/sparql-query-to-get-only-results-with-the-most-recent-date#36183745) which I am now able to implement with your help. — Belda, Jul 21 '17 at 06:49

How to project additional variables from SPARQL aggregates

1 Answers1