Shortcut: WD:RAQ

Wikidata:Request a query

From Wikidata
Jump to: navigation, search

Project
chat

Administrators'
noticeboard

Development
team

Translators'
noticeboard

Requests
for permissions

Interwiki
conflicts

Requests
for deletions

Property
proposal

Properties
for deletion

Requests
for comment

Partnerships
and imports

Request
a query

Bot
requests

On this page, old discussions are archived. An overview of all archives can be found at this page's archive index. The current archive is located at 2017/01.
fishing in the Wikidata river

This is a page where Wikidata SPARQL queries can be requested. Please provide feedback if a query is written for you.

For sample queries, see Examples and Wikidata:SPARQL query service/queries. Property talk pages include also summary queries for these.

For help writing your own queries, or other questions about queries, see Wikidata talk:SPARQL query service/queries.

A Christmassy request[edit]

Hoi, is there a way to query the kings that existed east of Bethlehem who lived in the year 0CE ? Thanks, GerardM (talk) 16:18, 27 December 2016 (UTC)

SELECT DISTINCT ?item ?itemLabel WHERE {
	?item wdt:P39/wdt:P279* wd:Q116;
          wdt:P569 ?dateofbirth;
          wdt:P570 ?dateofdeath.
    FILTER(year(?dateofbirth) < 0)
    FILTER(year(?dateofdeath) > 0)
    {?item wdt:P20 ?place} union {?item wdt:P19 ?place}
    ?place p:P625/psv:P625/wikibase:geoLatitude ?lat .
    wd:Q5776 p:P625/psv:P625/wikibase:geoLatitude ?latBe .
    FILTER(?lat > ?latBe)
	SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
Try it! --Pasleim (talk) 17:14, 27 December 2016 (UTC)
I did. But I am afraid that the east includes Rome and it manifestly does not. So what went wrong? Thanks, GerardM (talk) 11:55, 30 December 2016 (UTC)
@GerardM: I think Pasleim should have used longitude.
?place p:P625/psv:P625/wikibase:geoLongitude ?lon .
wd:Q5776 p:P625/psv:P625/wikibase:geoLongitude ?lonBe .
FILTER(?lon > ?lonBe)
Matěj Suchánek (talk) 13:53, 8 January 2017 (UTC)

Querying most important scientists[edit]

Hi. I'm looking to construct a query that returns a table that I can download as a csv file and ultimately import into a popular flashcard program (Anki) for study. Specifically, I'd like to construct a query that returns a list of scientists along with an image link and a number of key details about their lives such as date of birth, date of death, place of birth, place of death, spouse (if any), prominent occupation(s), country affiliation, and so on. I will list the properties I'm interested in below. Secondly, I'd like this list to be ordered according to some heuristic (such as rewards received or how many different languages the person has an article in) that measures/approximates a scientist's relevancy or importance, and then limit the list to, say, the top 500 "most important" scientists.

I tried constructing my own query but I ran into several problems and I'm too new to SPARQL and wikidata to know how to fix or work around them quickly.

One problem I noticed is that not all scientists are classed as such (e.g. scientist is not listed as one of Isaac Newton's occupations). According to one set of queries I did there are some 9630ish physicists on wikidata but only 2830ish scientists. Is there a way to work around this easily? If not then I can use separate queries for each occupation I am interested in.

Another problem I was having is determining a good heuristic for estimating the importance of each person. Ultimately something like (unique) page views on wikipedia would be optimal, but as far as I know you can't get that data through a query. I'm not very familiar with what all you can get through wikidata yet. Ideas on how to best determine importance with the information that's on wikidata would be very welcome.

Another thing I would like (to know how) to do is concatenate values into one cell on the table, especially when a property is associated with more than one value. Right now when I run a query and a scientist has, say, two date born values then a table with two rows for that scientist will be returned, each with the distinct dates born. I would like to do something like concatenate these values or simply select the first one.

I would also like (to know how) to filter the results by death year (e.g. only return scientists that died after 1600 and before 1940).

Finally, I would like to know if there's a way to download all at once the linked images or other media files that a query may return. I need these, in addition to the csv, in order to construct a flashcard deck in Anki that contains images or other media. Right now I'm expecting that I'll have to write a little python script to scrape the images off wikimedia, but it would be the bee's knees if there was a way to download all the media without doing that (and without doing it manually, either).

As for the key details I mentioned above, I'm interested in most of the basic information that would appear on a person's infobox on Wikipedia. Specifically: 0. Name 1. An image 2. Description (the one in the box at the top of a person's page on wikidata) 3. Place of birth 4. Place of death (optional; to include scientists that have not died) 5. Date of birth 6. Date of death (optional) 7. Nationality or country/ies of citizenship 8. Gender 9. Spouse Name (optional) 10. Educated at (optional) 11. Taught at (optional) 12. Awards received (optional) 13. Magnum opus (optional)

I would be very grateful to anyone who can either construct the query for me or would help me traverse the learning curve in constructing my own query.

To get all scientist you can query for all items with claim occupation (P106)=scientist (Q901) or subclasses of scientist (Q901). This can be achieved by
?item wdt:P106/wdt:P279* wd:Q901 .
We can't query for page views. Only data which is entered here by users is queryable. A possible heuristic for estimating the importance of a person is the number of sitelinks to Wikipedia and sister projects. That number you can get by
?item wikibase:sitelinks ?cnt .
So a good starting point for you could be
SELECT DISTINCT ?item WHERE{
  ?item wdt:P106/wdt:P279* wd:Q901;
        wikibase:sitelinks ?cnt        
} ORDER BY DESC(?cnt) LIMIT 500
Try it!
or with labels
SELECT ?item ?itemLabel WHERE{
  {
    SELECT DISTINCT ?item ?cnt WHERE{
      ?item wdt:P106/wdt:P279* wd:Q901;
            wikibase:sitelinks ?cnt
    } ORDER BY DESC(?cnt) LIMIT 500
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }        
} ORDER BY DESC(?cnt)
Try it! --Pasleim (talk) 09:33, 30 December 2016 (UTC)
Cool. I didn't know you could query for links to wikipedia. That should work well enough. I'm still running up against the 'scientist' category not including certain individuals and also including individuals that I don't want (such as Ezra Pound). I think I'm going to simply work around by performing a bunch of queries using categories like 'physicist', 'chemist', and 'geologist' instead. Moving on to the next problem of concatenating values. I have this code:
SELECT ?person ?personLabel (GROUP_CONCAT(?nationality;separator=', ') AS ?nationalities) WHERE{
  { 
    SELECT DISTINCT ?person ?cnt ?nationality WHERE{
      ?person wdt:P106/wdt:P279* wd:Q901;
            wikibase:sitelinks ?cnt .  
      ?person wdt:P27 ?nationality .
    } ORDER BY DESC(?cnt) LIMIT 500
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }        
} 
GROUP BY ?person ?personLabel
ORDER BY DESC(?cnt)
Try it!
The problem is that group_concat turns the entity into a url string. When I change ?nationality in the group_concat statement into ?nationalityLabel nothing shows up in the table. How does one turn the entity into its label string before putting it into the group_concat statement?
Edit: Oh and another thing I was wondering about: is there a way to simply select just the first value of a property? For instance Joseph Priestley has two values for his country of citizenship property: the United Kingdom and the United States. Is there a way to select just the first one (in this case the United Kingdom)?
--The Magical Septicorn 19:42, 30 December 2016 (UTC)
I have found the solution to this problem:
SELECT ?person ?personLabel ?personDescription 
(GROUP_CONCAT(DISTINCT ?nationality_label ;separator=', ') AS ?nationalities) 

WHERE{
  { 
    SELECT DISTINCT ?person ?cnt ?nationality_label WHERE{
      ?person wdt:P31 wd:Q5 . #eliminates fictional people
      ?person wdt:P106 wd:Q901; #selects occupation (apparently using wdt:P106/wdt:P279* instead of wdt:106 causes a time-out)
            wikibase:sitelinks ?cnt .  
      
      
      OPTIONAL {
            ?person wdt:P27 ?nationality .
            ?nationality rdfs:label ?nationality_label FILTER (lang(?nationality_label) = "en") . 
      }
                    
    } ORDER BY DESC(?cnt) 
    LIMIT 500
  }
  
  SERVICE wikibase:label {
       bd:serviceParam wikibase:language "en" .
    }
  
} 
GROUP BY ?person ?personLabel ?personDescription
Try it!
Still not sure how to select the first value of a property. I know SAMPLE selects a random value.
--The Magical Septicorn 21:39, 1 January 2017 (UTC)
What does it mean "the first value"? What is the ordering criterium? Matěj Suchánek (talk) 09:21, 2 January 2017 (UTC)
What rules does wikidata use to order the occupations of, say, Isaac Newton such that mathematician and physicist show up above theologian, alchemist, and politician?
Specifically I'm trying to solve a problem with this query:
SELECT DISTINCT ?person ?personLabel ?personDescription ?genderLabel  ?birthplaceLabel ?deathplaceLabel
(IF(YEAR(?birthdate)>1400,?birthdate,YEAR(?birthdate)) AS ?birthdate)
(IF(YEAR(?deathdate)>1400,?deathdate,YEAR(?deathdate)) AS ?deathdate)
(GROUP_CONCAT(DISTINCT ?nationality_label ;separator=', ') AS ?nationalities) 
(SAMPLE(?image) AS ?image) 
(GROUP_CONCAT(DISTINCT ?book_label ;separator=', ') AS ?books)
(GROUP_CONCAT(DISTINCT ?honour_label ;separator=', ') AS ?honours)

WHERE{
  {
    SELECT DISTINCT ?person ?image ?gender ?birthdate ?birthplace ?deathdate ?deathplace ?cnt ?nationality_label ?book_label ?honour_label WHERE{
      ?person wdt:P31 wd:Q5 . #eliminates fictional people
      ?person wdt:P106 wd:Q170790; #selects occupation
            wikibase:sitelinks ?cnt .  
      ?person wdt:P18 ?image .
      ?person wdt:P21 ?gender .
      OPTIONAL {?person wdt:P569 ?birthdate .}
      OPTIONAL {?person wdt:P570 ?deathdate .}
      OPTIONAL {?person wdt:P19 ?birthplace .}
      OPTIONAL {?person wdt:P20 ?deathplace .}
      OPTIONAL {
            ?person wdt:P27 ?nationality .
            ?nationality rdfs:label ?nationality_label FILTER (lang(?nationality_label) = "en") . 
      }
      OPTIONAL {
        	?book wdt:P50 ?person .
            FILTER EXISTS {
              ?book wdt:P31 wd:Q571 . #eliminates quotations and the like
            }
            ?book rdfs:label ?book_label FILTER (lang(?book_label) = "en") . 
      }
      OPTIONAL {
            ?person wdt:P166 ?honour .
            ?honour rdfs:label ?honour_label FILTER (lang(?honour_label) = "en") .
        }              
    } ORDER BY DESC(?cnt) 
  }
  SERVICE wikibase:label {
       bd:serviceParam wikibase:language "en" .
    }
} 
GROUP BY ?person ?personLabel ?personDescription ?genderLabel ?birthdate ?birthplaceLabel ?deathdate ?deathplaceLabel
ORDER BY DESC(?cnt)
LIMIT 300
Try it!
The problem is that I want to select the "best" value for the variables like ?birthdate and ?deathdate, and I also want to select the "best", say, three values for ?books and ?honours. I have had no luck with google trying to figure this out.

--The Magical Septicorn 22:17, 7 January 2017 (UTC)

P31:X with and without startdate[edit]

I am looking for 1: items with P31:Q14839548 with a specific startdates (as qualifiers) and 2: those without startdate.

3: And if there are startdates with other values than "1990-12-31/1995-12-31/2000-12-31/2005-12-31/2010-12-31/2015-12-31", I want to find them. Hopefully, they do not exists.

-- Innocent bystander (talk) 08:59, 30 December 2016 (UTC)

Item with a startdate:
SELECT ?item ?startdate WHERE{
  ?item p:P31 ?node .
  ?node ps:P31 wd:Q14839548; 
        pq:P580 ?startdate .
}
Try it!
Items without a startdate:
SELECT ?item  WHERE{
  ?item p:P31 ?node .
  ?node ps:P31 wd:Q14839548 .
  OPTIONAL{
    ?node pq:P580 ?startdate .
  }
  FILTER(!bound(?startdate))
}
Try it!
Items with a wrong startdate
SELECT DISTINCT ?item ?startdate WHERE{
  ?item p:P31 ?node .
  ?node ps:P31 wd:Q14839548 
  {
    ?node pq:P580 ?startdate .
    FILTER (day(?startdate) != 31)
   } UNION {
    ?node pq:P580 ?startdate .
    FILTER (month(?startdate) != 12)
   } UNION {
    ?node pq:P580 ?startdate .
    FILTER (year(?startdate) not in (1990, 1995, 2000, 2005, 2010, 2015))
   } 
}
Try it! --Pasleim (talk) 12:14, 30 December 2016 (UTC)
Big thanks Pasleim! But pardon a query-idiot, I would in the first example list only those who have startdate: "1990-12-31" (and in another search 1995-12-31). How do I modify your code to do that? Now it contains all dates. -- Innocent bystander (talk) 14:18, 30 December 2016 (UTC)
SELECT ?item ?startdate WHERE{
  ?item p:P31 ?node .
  ?node ps:P31 wd:Q14839548; 
        pq:P580 ?startdate .
  FILTER(str(?startdate) = "1990-12-31T00:00:00Z")
}
Try it! --Pasleim (talk) 14:21, 30 December 2016 (UTC)
Many thanks! As you understand. The sum of these lists are supposed to be equivalent to a list of all Q14839548. -- Innocent bystander (talk) 14:45, 30 December 2016 (UTC)
This works lovely with ListeriaBot and my projects! Now I try to implement this also for urban area of Sweden (Q12813115). The problem here is that the valid dates are not always December 31. 1960-75 it was November 1 and in 1980 it was September 15. Is it possible to modify the "wrong startdate"-code to handle this? -- Innocent bystander (talk) 15:04, 2 January 2017 (UTC)
@Pasleim: You maybe not noticed this, or you are maybe busy?! That's ok, but anybody can feel free to answer me. Valid startdates are December 31 some years, November 1 some years, and September 15 in 1980. Could this be solved? Ideally, no other dates should be used, but sometimes we make mistakes. It is those cases I am looking for! -- Innocent bystander (talk) 14:56, 3 January 2017 (UTC)
In that case it's probably the easiest just to list all allowed dates explicitly:
SELECT ?item ?startdate ?date WHERE{
  ?item p:P31 ?node .
  ?node ps:P31 wd:Q12813115; 
        pq:P580 ?startdate .
  BIND(SUBSTR(STR(?startdate), 0, 10) as ?date)
  FILTER(?date NOT IN ("1960-11-01","1965-11-01","1970-11-01","1975-11-01","1980-09-15","1985-12-31","1990-12-31","1995-12-31","2000-12-31","2005-12-31","2010-12-31","2015-12-31"))
}
Try it! --Pasleim (talk) 09:36, 4 January 2017 (UTC)

Including items with statements only in deprecated rank[edit]

[1] Is there a way to include the items that have an item in head coach (P286) with deprecated rank? I mean if the item had only one value with P286 and this value is in deprecated rank, is must included in my results. If the item has two values, one deprecated and the other not deprecated (normal or preferred) then the item should not be included be in my results.

For example, Doxa Katokopias F.C. (Q1253472) must be included on my results. It has one value for P286 but with deprecated rank. Xaris333 (talk) 02:22, 31 December 2016 (UTC)

I think I answered it at Wikidata:Project_chat#Help_for_queries. Sorry for the delay.
--- Jura 10:41, 31 December 2016 (UTC)

Timeout when finding distance between two entities[edit]

This code is supposed to return the distance between two nodes in Wikidata. Essentially it gets the paths from A and from B to their nearest common ancestor.

select ?a ?b ?super (?aLength + ?bLength as ?length)
{
  values (?a ?b) { (wd:Q5 wd:Q349) }
  { 
    select ?a ?super (count(?mid) as ?aLength) { 
      ?a wdt:P279* ?mid .
      ?mid wdt:P279+ ?super .
    }
    group by ?a ?super
  }
  { 
    select ?b ?super (count(?mid) as ?bLength) { 
      ?b wdt:P279* ?mid .
      ?mid wdt:P279+ ?super .
    }
    group by ?b ?super
  }
}
order by ?length
limit 1

Try it!

The issue is that I'm getting timeouts. Any ideas how to fix it? For the record I had this question here too, but no one replied. (and I'm not familiar how to properly my code here :-/)

Or in plain english, I'm looking for something to characterize the connection between "Notre Dame School of Architecture" and "college". Intuitively speaking they are related (with respect to some certain distance measure) but I am not sure how to characterize this similarity based on Wikidata.

You could try out the RDF GAS API by blazegraph:
PREFIX gas: <http://www.bigdata.com/rdf/gas#>

SELECT ?super (?aLength + ?bLength as ?length) WHERE {
  SERVICE gas:service {
    gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.SSSP" ;
                gas:in wd:Q5 ;
                gas:traversalDirection "Forward" ;
                gas:out ?super ;
                gas:out1 ?aLength ;
                gas:maxIterations 10 ;
                gas:linkType wdt:P279 .
  }
  SERVICE gas:service {
    gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.SSSP" ;
                gas:in wd:Q349 ;
                gas:traversalDirection "Forward" ;
                gas:out ?super ;
                gas:out1 ?bLength ;
                gas:maxIterations 10 ;
                gas:linkType wdt:P279 .
  }  
} ORDER BY ?length
LIMIT 1
Try it! --Pasleim (talk) 09:48, 4 January 2017 (UTC)


Nice thanks! Danyaljj (talk) 16:47, 4 January 2017 (UTC)

One more question: how could you query this with the rest api? I tried adding your code to `https://query.wikidata.org/sparql?format=json&query=` and it gives me error. Where am I going wrong? Here is an example. Danyaljj (talk) 17:47, 4 January 2017 (UTC)

You need to add the prefix, PREFIX gas: <http://www.bigdata.com/rdf/gas#> is not part of the standard prefix list. This should work. --Pasleim (talk) 08:54, 5 January 2017 (UTC)

Wonderful! Thanks! Danyaljj (talk) 23:36, 7 January 2017 (UTC)

Actually one more question: is it possible to do disjunction between the edge types? Like either `wdt:P279` or `wdt:P31`? Danyaljj (talk) 23:59, 7 January 2017 (UTC)

In other words the path the two concepts/entities could be of either types of `wdt:P279` or `wdt:P31`. Danyaljj (talk) 03:26, 8 January 2017 (UTC)

Greek mythology[edit]

All the items that either have part of (P361)  Ancient Greek mythology (Q34726) or have instance of (P31)  Z/subclass of (P279)  Z where Z is an item that either has part of (P361)  Ancient Greek mythology (Q34726) or has instance of (P31)  Z/subclass of (P279)  Z. I hope to be clear enough. Thanks, --Epìdosis 14:17, 4 January 2017 (UTC)

I'm not sure which of these two you want:
SELECT ?item ?itemLabel {
  ?item (wdt:P31/wdt:P279*)?/wdt:P361 wd:Q34726 .
  # this times out
  # ?item (wdt:P31/wdt:P279*)*/wdt:P361 wd:Q34726 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
Try it!
Matěj Suchánek (talk) 15:33, 4 January 2017 (UTC)
"malformed query". And as usual it's not understandable why. --Infovarius (talk) 18:51, 6 January 2017 (UTC)
Fixed (got it immediately, whitespace issue). Matěj Suchánek (talk) 21:09, 7 January 2017 (UTC)

Translation Tier[edit]

Can I get a list of featured articles on other Wikipedia's that do not have a corresponding article in the English Wikipedia. I am working towards reducing the Anglophile nation bias on the English Wikipedia, and other editors suggested translating featured articles first. I am an SQL novice and have no idea on how to go about this.Jupitus Smart (talk) 02:59, 5 January 2017 (UTC)

Wikidata:Request_a_query/Archive/2016/10#Median-length_featured_article_in_specific_languages has an elegant (if slow) query for "topics with the most FAs". Unfortunately, adding a "not in English" filter to it breaks down quite fast. Not sure how else to do this. Andrew Gray (talk) 12:38, 5 January 2017 (UTC)
@Jupitus Smart, Andrew Gray: I think this may do what you're looking for. Mduvekot (talk) 17:49, 5 January 2017 (UTC)
Thanks a lot Mduvekot. This is probably what I had in mind.Jupitus Smart (talk) 09:10, 6 January 2017 (UTC)
Aha, yes - I should have thought of restricting it to just one language for speed! 50 in Spanish, 128 in French, 355 (!) in German, 107 in Russian, 93 in Polish... there's more than I was expecting. Sampling suggests a lot of these are one-language-only; it's interesting to see how niche many FAs are! Andrew Gray (talk) 19:43, 6 January 2017 (UTC)

Calendar dates in order[edit]

It would be nice to be able to use Wikidata generate calendars of scientists' birthdays, of discoveries, and so on. I'm thinking in particular of outreach to museums, libraries and similar institutions that run public events over a long period. A starting point for such a calendar is a list of dates in order. My attempt is at User:MartinPoulter/queries#Attempt_at_a_calendar_ordering_of_days_of_the_year:_breaks_down_pretty_quickly - not exactly a complete success. Is there an elegant solution? MartinPoulter (talk) 12:13, 5 January 2017 (UTC)

Something like this, perhaps?
SELECT ?date ?dateLabel ?monthNumber ?dayNumber WHERE {
  # a date item is a “determinator for date of periodic occurrence” and part of a month
  ?date wdt:P31 wd:Q14795564;
        wdt:P361 ?month.
  # a month is a month of the Gregorian calendar and has a month number
  ?month wdt:P279 wd:Q18602249;
         wdt:P2837 ?monthNumber.
  # February 30 and “first saturday in March” …
  FILTER(?date NOT IN (wd:Q37096, wd:Q21130129)).
  # and here it gets ugly, it seems there’s no way to get the date’s day except from the label
  ?date rdfs:label ?dateLabel. FILTER(LANG(?dateLabel) = "en").
  ?month rdfs:label ?monthLabel. FILTER(LANG(?monthLabel) = "en").
  FILTER(STRSTARTS(?dateLabel, CONCAT(?monthLabel, " "@en))). # sanity check
  BIND(xsd:integer(SUBSTR(?dateLabel, STRLEN(?monthLabel)+1)) AS ?dayNumber).
}
ORDER BY ?monthNumber ?dayNumber
Try it!
It parses the label to get the day number, which is pretty icky… I suppose that should be stored on the item somewhere (series ordinal qualifier on the part of statement?), does someone want to do a quick bot run? :) --TweetsFactsAndQueries (talk) 18:31, 5 January 2017 (UTC)
Thanks so much, User:TweetsFactsAndQueries. This is exactly what I was hoping for and I look forward to making some interesting calendars with it. MartinPoulter (talk) 15:34, 6 January 2017 (UTC)

People that held position no label (Q28128358)[edit]

I'm trying go generate a Listeria list for people that held this position no label (Q28128358). I would like for the list to have item number, article name, the start and end date for when the person held this position and an image. So far only Tom Forssner (Q5737866) has this position stated, but more to come. Is there something else needed in order to generate this query? //Mippzon (talk) 23:06, 5 January 2017 (UTC)

Maybe this works for you.
--- Jura 06:20, 6 January 2017 (UTC)
Thanks! Just was I was looking for and more! I only needed to replace the position with the one I was interested in. Thanks! //Mippzon (talk) 10:32, 6 January 2017 (UTC)

Languages without notable men[edit]

Can anyone list languages without any speaker (languages spoken, written or signed (P1412) and native language (P103)) on Wikidata? Better if sorted by a number of real speakers. --Infovarius (talk) 19:18, 6 January 2017 (UTC)

SELECT ?lang ?langLabel ?speakers {
  ?lang wdt:P279*/wdt:P31 wd:Q315 .
  FILTER NOT EXISTS { [] (wdt:P1412|wdt:P103) ?lang } .
  OPTIONAL { ?lang wdt:P1098 ?speakers } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
} ORDER BY DESC(?speakers)
Try it!
Matěj Suchánek (talk) 21:06, 7 January 2017 (UTC)


Or directly?
SELECT ?lang ?langLabel ?speakers {
  ?lang wdt:P1098 ?speakers .
  FILTER NOT EXISTS { [] (wdt:P1412|wdt:P103) ?lang } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
} ORDER BY DESC(?speakers)
Try it!
Given that P1412 was largely fed by BnF, it might be a list of languages in which there are no books held?
--- Jura 07:18, 8 January 2017 (UTC)

What are the birds taxa with no authors provided? (example)[edit]

Hello all, I have been trying by myself but did not succed. Could someone create a request for having a list of taxons with missing authors, so that I can fill some of them? I would like:

Thanks in advance, Totodu74 (talk) 16:04, 7 January 2017 (UTC)

SELECT ?item ?name {
  #?item wdt:P31/wdt:P279? wd:Q16521 .
  ?item p:P225 ?st .
  FILTER NOT EXISTS { ?st (pq:P405|pq:P574) [] } .
  ?st ps:P225 ?name .
  # uncomment and choose the root taxa
  #?item wdt:P171+ wd:Q156888 .
} LIMIT 100
Try it!
Matěj Suchánek (talk) 20:59, 7 January 2017 (UTC)
Thank you very much, Matěj Suchánek! That was quick! :) Totodu74 (talk) 22:36, 7 January 2017 (UTC)
Totodu74, your request about taxon author (P405) and date of taxon name publication (P574) is uncomplete: Do you want to add the author(s) and year of original combination (P1403) the to respective item? --Succu (talk) 22:28, 7 January 2017 (UTC)
I want to add author(s) and year to taxon name (P225), not necessarily to original combination (P1403). Totodu74 (talk) 22:35, 7 January 2017 (UTC)
@Totodu74: It may be possibe to import some of this data from Wikispecies. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 23:05, 7 January 2017 (UTC)
Yes, we could import a lot from Wikispecies, as well as from the French Wikipedia (on which we tried for years to always provide first names for authors, like [[Example Author|Author]], [[1864]] in the taxobox), and especially on some groups (herptiles, arachnids) this is very well done. Furthermore, nominal subspecies could inherit author citation from their parent (e.g. Sitta nagaensis nagaensis (Q27629808) from Sitta nagaensis (Q902434)). However, I am totally unable to develop any script to do so! :D Totodu74 (talk) 23:16, 7 January 2017 (UTC)

Item without sitelink with imported from (P143)= wikisomething...[edit]

Need a list of item without sitelink but that using like source: imported from (P143) = some wiki project (probably: instance of (P31) = Wikipedia language edition (Q10876391)) --ValterVB (talk) 10:23, 8 January 2017 (UTC)

@ValterVB: I got this atm. :
select ?item ?wikipedia ?prop where {
  ?item ?prop [
     prov:wasDerivedFrom [
       pr:P143 ?wikipedia
     ]
  ] .
  
  [] wikibase:claim ?prop .
  
  ?wikipedia wdt:P31 wd:Q10876391 .
  
  filter not exists {
     ?article schema:isPartOf []; 
              schema:about ?item
  }
} limit 1
Try it!
but it's still not complete : it returns the items with no sitelinks at all and I guess you want that the "imported from" do not have an article in that precise Wikipédia. I'll try something. author  TomT0m / talk page 13:23, 8 January 2017 (UTC)

@ValterVB: This is it :

select ?item ?prop ?code where {
  ?item ?prop [
     prov:wasDerivedFrom [
       pr:P143 ?wikipedia
     ]
  ] .
  
  [] wikibase:claim ?prop .
  
  ?wikipedia wdt:P31 wd:Q10876391 .
  
  filter not exists {
     ?article schema:isPartOf ?articlewp; 
              schema:about ?item .
     bind(concat("https://", ?code, ".wikipedia.org/") as ?wpurl)
     filter(str(?articlewp) = ?wpurl) .
  
  }
  ?wikipedia wdt:P424 ?code .
} limit 10

Try it!

@TomT0m: Thanks, to begin the first is perfect, but work only for "Limit=1", not so useful. I tried with "COUNT(DISTINCT ...)" to know how many item are envolved but don't work for time out. Maybe I must work on dump. --ValterVB (talk) 14:02, 8 January 2017 (UTC)
(My 100,000th edit!) If you're lucky, this sometimes work (and sometimes not):
SELECT ?item ?prop ?wiki ?url {
  ?wiki wdt:P31 wd:Q10876391;
        wdt:P856 ?url;
        ^pr:P143/^prov:wasDerivedFrom ?st .
  ?item ?prop ?st .
  [] wikibase:claim ?prop .
  FILTER NOT EXISTS { ?item ^schema:about/schema:isPartOf ?url } .
} LIMIT 10
Try it!
Matěj Suchánek (talk) 14:27, 8 January 2017 (UTC)

Currencies as units[edit]

I have this query listing companies by its revenue:

SELECT ?item ?itemLabel ?revenue ?hqLabel 
WHERE
{
	?item wdt:P31 wd:Q4830453 .
    ?item wdt:P159 ?hq . 
    ?item wdt:P2139 ?revenue . 
	SERVICE wikibase:label { bd:serviceParam wikibase:language "cs, en" }
}
ORDER BY DESC(?revenue)

Try it!

But actually it is not listing. So there are two tasks:

Quite complicated but is at least partly possible. The most notable problems are:
  1. I have to leave the simple wdt: predicate and use p:-like ones. These ones do not support "choose best value" strategy. Although the documentation suggests looking for a special "best" rank, it's nowhere in the database.
  2. Your query is not bound to a specific time, some companies have multiple revenues per multiple dates (I'm not sure whether ranks should be applied).
  3. Some currencies don't use the same conversion system (if any), like Q131016#P2284.
Anyway, this is it:
SELECT ?item ?itemLabel ?revenue ?unitLabel ?hqLabel (?usd * ?revenue AS ?revenue_USD) ?date
WHERE
{
  ?item wdt:P31 wd:Q4830453;
        wdt:P159 ?hq;
        p:P2139 ?statement .
  FILTER NOT EXISTS { ?statement wikibase:rank wikibase:DeprecatedRank } .
  FILTER NOT EXISTS {
    ?item p:P2139 ?statement1 .
    ?statement1 wikibase:rank wikibase:PreferredRank .
    FILTER( ?statement1 != ?statement ) .
  } .
  OPTIONAL { ?statement pq:P585 ?date } .
  ?statement psv:P2139 [
    wikibase:quantityAmount ?revenue; wikibase:quantityUnit ?unit
  ] .
  ?unit p:P2284 ?unit_statement .
  FILTER NOT EXISTS { ?unit_statement wikibase:rank wikibase:DeprecatedRank } .
  FILTER NOT EXISTS {
    ?unit p:P2284 ?unit_statement1 .
    ?unit_statement1 wikibase:rank wikibase:PreferredRank .
    FILTER( ?unit_statement1 != ?unit_statement ) .
  } .
  ?unit_statement psv:P2284 [
    wikibase:quantityUnit wd:Q4917; wikibase:quantityAmount ?usd
  ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "cs,en" } .
}
ORDER BY DESC(?revenue_USD)
Try it!
Matěj Suchánek (talk) 15:42, 9 January 2017 (UTC)
Thanks a let for effort, but still getting error for that query. Concerning the ranks (and dates), current requirement for revenue is to use value with preferred rank (that is most recent one). For price (of currency) the answer is pretty tricky, as the most correct is "the appropriate one", but I can understand that pairing the company P2139:P858:"2015" with the currency P2284:P858:"31 Dec 2015") automatically is almost impossible (and still leaving aside the question of possible different ending of fiscal year versus calendar year). So current requirement is again to use value with preferred rank. I know that most easiest will be to insert P2139 in same currencies for all companies, but I think that is not proper.--Jklamo (talk) 17:10, 10 January 2017 (UTC)
@Matěj Suchánek: Any update? --Jklamo (talk) 18:14, 12 January 2017 (UTC)
Sorry, missed this. There was (again) a whitespace problem which seems to be caused by copypasting into NWE. Matěj Suchánek (talk) 19:08, 12 January 2017 (UTC)
Thanks for fix. --Jklamo (talk) 11:31, 13 January 2017 (UTC)
@Matěj Suchánek: I noticed that items with Q4917 were omitted, so I tried to fix it. But I am not able to handle ranks properly.
SELECT ?item ?itemLabel ?revenue ?unitLabel ?hqLabel (?usd * ?revenue AS ?revenue_USD) ?date
WHERE
{
  { ?item wdt:P31 wd:Q4830453;
        wdt:P159 ?hq;
        p:P2139 ?statement .
  FILTER NOT EXISTS { ?statement wikibase:rank wikibase:DeprecatedRank } .
  FILTER NOT EXISTS {
    ?item p:P2139 ?statement1 .
    ?statement1 wikibase:rank wikibase:PreferredRank .
    FILTER( ?statement1 != ?statement ) .
  } .
  OPTIONAL { ?statement pq:P585 ?date } .
  ?statement psv:P2139 [
    wikibase:quantityAmount ?revenue; wikibase:quantityUnit ?unit
  ] .
  ?unit p:P2284 ?unit_statement .
  FILTER NOT EXISTS { ?unit_statement wikibase:rank wikibase:DeprecatedRank } .
  FILTER NOT EXISTS {
    ?unit p:P2284 ?unit_statement1 .
    ?unit_statement1 wikibase:rank wikibase:PreferredRank .
    FILTER( ?unit_statement1 != ?unit_statement ) .
  } .
  ?unit_statement psv:P2284 [
    wikibase:quantityUnit wd:Q4917; wikibase:quantityAmount ?usd
  ] .
  } 
  UNION
  {
  ?item wdt:P31 wd:Q4830453;
        wdt:P159 ?hq;
        p:P2139 ?statement .
  ?statement psv:P2139 [
  wikibase:quantityUnit wd:Q4917; wikibase:quantityAmount ?revenue_USD
  ] .    
  FILTER NOT EXISTS { ?statement wikibase:rank wikibase:DeprecatedRank } .
  FILTER NOT EXISTS {
    ?item p:P2139 ?statement1 .
    ?statement1 wikibase:rank wikibase:PreferredRank .
    FILTER( ?statement1 != ?statement ) .
  } .
  OPTIONAL { ?statement pq:P585 ?date } .
  } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "cs,en" } .
}
ORDER BY DESC(?revenue_USD)
Try it!. Jklamo (talk) 22:54, 14 January 2017 (UTC)

(*facepalm*) Then finally:

SELECT ?item ?itemLabel ?revenue ?unitLabel ?hqLabel ?revenue_USD ?date WHERE {
  ?item wdt:P31 wd:Q4830453;
        wdt:P159 ?hq;
        p:P2139 ?statement .
  FILTER NOT EXISTS { ?statement wikibase:rank wikibase:DeprecatedRank } .
  FILTER NOT EXISTS {
    ?item p:P2139 ?statement1 .
    ?statement1 wikibase:rank wikibase:PreferredRank .
    FILTER( ?statement1 != ?statement ) .
  } .
  OPTIONAL { ?statement pq:P585 ?date } .
  {
    ?statement psv:P2139 [
      wikibase:quantityAmount ?revenue; wikibase:quantityUnit wd:Q4917
    ] .
    BIND( wd:Q4917 AS ?unit ) .
    BIND( ?revenue AS ?revenue_USD ) .
  } UNION {
    ?statement psv:P2139 [
      wikibase:quantityAmount ?revenue; wikibase:quantityUnit ?unit
    ] .
    FILTER( ?unit != wd:Q4917 ) .
    ?unit p:P2284 ?unit_statement .
    ?unit_statement psv:P2284 [
      wikibase:quantityUnit wd:Q4917; wikibase:quantityAmount ?usd
    ] .
    FILTER NOT EXISTS { ?unit_statement wikibase:rank wikibase:DeprecatedRank } .
    FILTER NOT EXISTS {
      ?unit p:P2284 ?unit_statement1 .
      ?unit_statement1 wikibase:rank wikibase:PreferredRank;
                       psv:P2284/wikibase:quantityUnit wd:Q4917 .
      FILTER( ?unit_statement1 != ?unit_statement ) .
    } .
    BIND( ?revenue * ?usd AS ?revenue_USD ) .
  } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "cs,en" } .
}
ORDER BY DESC(?revenue_USD)

Try it!

Matěj Suchánek (talk) 09:26, 15 January 2017 (UTC)

BugGuide ID verification[edit]

BugGuide ID (P2464) is a page ID for BugGuide.net which is restricted only to North America Arthropoda. I was trying to create a query to verify the "Arthropoda" part. Unfortunately my query below is not working (timing out despite low number of items with that property). What am I doing wrong? --Jarekt (talk) 14:20, 9 January 2017 (UTC)

SELECT DISTINCT ?item ?BugGuideID ?taxon 
{
	?item wdt:P2464 ?BugGuideID .  # BugGuideID 
	?item wdt:P225  ?taxon .       # taxon name
	MINUS{ ?item wdt:P171*  wd:Q1360 . }    # parent items are not Arthropoda
}

Try it!

I just noticed Wikidata:Request_a_query#What_are_the_birds_taxa_with_no_authors_provided.3F_.28example.29 above May be I should be using MINUS{ ?item wdt:P171+ wd:Q1360 . }? It still does not help. --Jarekt (talk) 14:28, 9 January 2017 (UTC)
Looks like this bug: Wikidata:Contact the development team/Archive/2016/09#SPARQL: (wdt:P171)*.
* means how many times possible, + means how many times possible, at least once. Matěj Suchánek (talk) 15:59, 9 January 2017 (UTC)
Matěj Suchánek, thank you for your help. My SPARQL is still quite shaky and I am trying new things. This discussion between User:Succu and User:Smalyshev (WMF) happen in September. Is there some hope of resolving it? Any workarounds that might work? --Jarekt (talk) 16:58, 9 January 2017 (UTC)
I didn't found a workaround and had to stop or modify some of my bot tasks. --Succu (talk) 17:06, 9 January 2017 (UTC)

Query

SELECT DISTINCT ?item ?BugGuideID ?taxon 
{
    ?item wdt:P2464 ?BugGuideID .  # BugGuideID 
    ?item wdt:P225  ?taxon .       # taxon name
    MINUS {?item wdt:P171|
                 wdt:P171/wdt:P171|
                 wdt:P171/wdt:P171/wdt:P171|
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171|
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171| 
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|  
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|   
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|    
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|    
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|     
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|     
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|  
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|   
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171|
                 wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171/wdt:P171
                 wd:Q1360 . }
    MINUS{ ?item wdt:P105 wd:Q38348. }
}

Try it!

is not very pretty but it works. --Jarekt (talk) 18:20, 9 January 2017 (UTC)

Yes, until the path is deeper than 20. ;) --Succu (talk) 18:32, 9 January 2017 (UTC)

git[edit]

Hi,

Could someone create a SPARQL request that list all items that have source code repository (P1324) with qualifier protocol (P2700) = git (Q186055) but the URL doesn't finish with .git? I'm trying to fix mistakes like this.

Thanks! --Thibaut120094 (talk) 09:10, 11 January 2017 (UTC)

Bonus point: If you can also create me a request that list all items that have instance of (P31) = free software (Q341) but no Debian package (P3442) and no Free Software Directory entry (P2537), I'll be very grateful.

SELECT ?item ?itemLabel ?string ?rank {
  ?item p:P1324 [
    pq:P2700 wd:Q186055; ps:P1324 ?string; wikibase:rank ?rank
  ] .
  FILTER ( !STRENDS( STR( ?string ), ".git" ) ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
Try it!
SELECT ?item ?itemLabel ?itemDescription {
  ?item wdt:P31 wd:Q341 .
  MINUS { ?item wdt:P3442 [] } .
  MINUS { ?item wdt:P2537 [] } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}
Try it!
Matěj Suchánek (talk) 15:30, 11 January 2017 (UTC)
Thank you very much. --Thibaut120094 (talk) 15:54, 11 January 2017 (UTC)

Who is entering in public domain this year (life+50)[edit]

Hello, I would need a little help please.

I'm looking for people entering in the public domain (Canada life+50yrs).

So:

Result:

Item Name sex or gender (P21) occupation (P106) country of citizenship (P27) place of birth (P19) place of death (P20) date of death (P570)
Q127018 Pierre Mercure male composer Canada Montreal Avallon
Q103949 Alberto Giacometti male sculptor, artist, painter and writer Switzerland and France Stampa Chur
Q3100674 Geneviève Massignon female linguist, writer and children's writer France Paris
Q3505937 Suzanne Césaire female writer France Les Trois-Îlets Yvelines
Q3184398 Joseph-Papin Archambault male Catholic priest Canada Montreal

Thank you very much in advance for your help. Best regards, Benoit Rochon (talk) 17:59, 11 January 2017 (UTC)

This is what you can start with:
SELECT ?item ?itemLabel ?genderLabel ?countryLabel ?place_birth ?place_death ?dod {
   ?item wdt:P31 wd:Q5;
               wdt:P21 ?gender;
               wdt:P570 ?dod .
   FILTER( YEAR( ?dod ) = 1966 ) .
   OPTIONAL { ?item wdt:P27 ?country } .
   OPTIONAL { ?item wdt:P19 ?place_birth } .
   OPTIONAL { ?item wdt:P20 ?place_death } .
   FILTER( BOUND( ?country )|| BOUND( ?place_birth ) || BOUND( ?place_birth ) ) .
   SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
} ORDER BY ?gender ?dod
Try it!
Do you need some more filtering criteria? Matěj Suchánek (talk) 17:10, 12 January 2017 (UTC)

Thank you very much Matěj Suchánek, this is very good thank you. But wow, this is a heavy query. Maybe we can leave place of birth (P19) and place of death (P20), because occupation (P106) is more important. So maybe something like this is more light:

SELECT ?item ?itemLabel ?genderLabel ?occupationLabel ?countryLabel ?death {
  ?item wdt:P31 wd:Q5;
        wdt:P21 ?gender;
        wdt:P106 ?occupation;
        wdt:P27 wd:Q16;
        wdt:P570 ?death .
  FILTER( YEAR( ?death ) = 1967 ) .
  OPTIONAL { ?item wdt:P27 ?country } .
  FILTER( BOUND( ?country ) ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en" } .
} ORDER BY ?itemLabel

Try it!

Now, would it be possible to :

  1. merge items that have multiple occupation (P106) in one single cell, separated by a comma (sometimes people have multiple occupation (P106)).
  2. merge items that have multiple country of citizenship (P27) (sometimes people have multiple country of citizenship (P27)).
  3. besides Canada (Q16), I would like the query include France (Q142), Switzerland (Q39), Belgium (Q31) for property country of citizenship (P27) is that possible ?

I any case, thank you for this. It will help a lot to target people that fall in public domain. Best regards, Benoit Rochon (talk) 00:57, 13 January 2017 (UTC)

3. Simply
SELECT ?item ?itemLabel ?genderLabel ?occupationLabel ?countryLabel ?death {
  ?item wdt:P31 wd:Q5;
        wdt:P21 ?gender;
        wdt:P106 ?occupation;
        wdt:P27 ?country;
        wdt:P570 ?death .
  VALUES ?country { wd:Q16 wd:Q39 wd:Q31 } .
  FILTER( YEAR( ?death ) = 1967 ) .
  OPTIONAL { ?item wdt:P27 ?country } .
  FILTER( BOUND( ?country ) ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en" } .
} ORDER BY ?itemLabel
Try it!
Leaving 1. and 2. on Matěj :-) --Jklamo (talk) 11:55, 13 January 2017 (UTC)


Guys... Matěj Suchánek, Jklamo, thank you SOOO much! We are very close to get exactly what I'm looking for, and I'm sure that other Wikimedians will love that query.

I changed few this to target creators, artists, etc. But it's getting an heavy query!

Now the last thing, is to merge "occupations" and "countries" to avoid redundant items. Anyone can show me how, or pointing another query that use this "function"?

SELECT ?item ?itemLabel ?genderLabel ?occupationLabel ?countryLabel ?death {
  ?item wdt:P31 wd:Q5;
        wdt:P21 ?gender;
        wdt:P106 ?occupation;
        wdt:P27 ?country;
        wdt:P570 ?death .
  VALUES ?occupation { wd:Q483501 wd:Q36834 wd:Q639669 wd:Q753110 wd:Q488205 wd:Q49757 wd:Q4964182 wd:Q1281618 wd:Q36180 wd:Q482980 wd:Q1028181 wd:Q6625963 wd:Q28389 wd:Q1930187 wd:Q33999 wd:Q3282637 wd:Q81096 wd:Q201788 wd:Q18939491 wd:Q486748 wd:Q3658608 wd:Q214917 wd:Q11774202 wd:Q205375 } . #occupation: composer, poet, sculptor, writer, artist, painter
  VALUES ?country { wd:Q16 wd:Q142 wd:Q39 wd:Q31 } . #countries: Canada, France, Switzerland, Belgium. Remove this line to get worldwide.
  FILTER( YEAR( ?death ) = 1967 ) .
  OPTIONAL { ?item wdt:P27 ?country } .
  FILTER( BOUND( ?country ) ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr,en,ru,el,es,fa" } .
} ORDER BY ?itemLabel

Try it!

Thank you again. Benoit Rochon (talk) 14:03, 13 January 2017 (UTC)

(edit conflict)
SELECT ?item ?itemLabel ?genderLabel (GROUP_CONCAT(DISTINCT ?occupationLabel; SEPARATOR=", ") AS ?occupations) (GROUP_CONCAT(DISTINCT ?countryLabel; SEPARATOR=", ") AS ?countries) ?death {
   VALUES ?target_country { wd:Q16 wd:Q31 wd:Q39 wd:Q142 } .
  VALUES ?occupation { wd:Q483501 wd:Q36834 wd:Q639669 wd:Q753110 wd:Q488205 wd:Q49757 wd:Q4964182 wd:Q1281618 wd:Q36180 wd:Q482980 wd:Q1028181 wd:Q6625963 wd:Q28389 wd:Q1930187 wd:Q33999 wd:Q3282637 wd:Q81096 wd:Q201788 wd:Q18939491 wd:Q486748 wd:Q3658608 wd:Q214917 wd:Q11774202 wd:Q205375 } . #occupation: composer, poet, sculptor, writer, artist, painter
   ?item wdt:P31 wd:Q5;
               wdt:P21 ?gender;
               wdt:P570 ?death;
               wdt:P27 ?target_country;
               wdt:P27 ?country;
               wdt:P106 ?occupation .
   FILTER( YEAR( ?death ) = 1966 ) .
   SERVICE wikibase:label {
       bd:serviceParam wikibase:language "fr,en,ru,el,es,fa" .
       ?item rdfs:label ?itemLabel .
       ?gender rdfs:label ?genderLabel .
       ?occupation rdfs:label ?occupationLabel .
       ?country rdfs:label ?countryLabel .
   } .
} GROUP BY ?item ?itemLabel ?genderLabel ?death ORDER BY ?genderLabel
Try it!
There are certainly better approaches but this is how far my skills have gone so far. Matěj Suchánek (talk) 14:21, 13 January 2017 (UTC)

Constraint with an complicated exception[edit]

Would it be possible to modify the P1448-constraint for P776, so that those who have "P31:småort (Q14839548) startdate:2015-12-31" have an automatic exception from this constraint? Statistics Sweden has not provided us with any "names" of new entities. -- Innocent bystander (talk) 18:01, 12 January 2017 (UTC)

SELECT DISTINCT ?item ?itemLabel ?n
{
	?item wdt:P776 ?value .
	FILTER NOT EXISTS { ?item wdt:P1448 [] } .
  	OPTIONAL { ?item p:P31 [ ps:P31 wd:Q14839548; pq:P580 ?n ] }
  	FILTER ( !BOUND(?n) \u007C\u007C ?n < "2015-12-31T00:00:00Z"^^xsd:dateTime )
	SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en" }
}

Try it!


--- Jura 19:08, 12 January 2017 (UTC)
Looks like it should work, but didn't we have a template for complex constraints? -- Innocent bystander (talk) 08:13, 13 January 2017 (UTC)
{{Complex constraint}}? Matěj Suchánek (talk) 14:24, 13 January 2017 (UTC)
Thanks! I'll try that! -- Innocent bystander (talk) 13:20, 15 January 2017 (UTC)

Items without labels in French[edit]

Hi all,

Wikidata no labels and Names as labels don't work anymore (apparently because Wikidata Query Service is closed). These tools was very useful for me. It's possible to have the same result (items-ID and labels in the source language) with a SPARQL queries?

My example: instance of (P31)  fourth level administrative division in Indonesia (Q2225692). Without label in French, labels source: Indonesian.

Thank you, Tubezlob (🙋) 12:53, 14 January 2017 (UTC)

My version:
SELECT ?item ?label_id WHERE {
  ?item wdt:P31 wd:Q2225692 .
  ?item rdfs:label ?label_id filter(lang(?label_id) = "id") .
  MINUS { ?item rdfs:label ?label_fr filter(lang(?label_fr) = "fr") . }
}
LIMIT 1000
Try it!

--Edgars2007 (talk) 12:59, 14 January 2017 (UTC)

@Edgars2007: Thank you very much for your quick answer! It's totally what I wanted! 👍 Tubezlob (🙋) 13:15, 14 January 2017 (UTC)

Query for awards of this year[edit]

Hoi, I would like to have a query that shows only the awards that are "award received", have a date in 2016. To make it more complicated, I only want those awards that have been added yesterday or have been added since the last time the query ran. To make it perfect it would show if the award exist on "this" Wikipedia. Thanks, GerardM (talk) 15:17, 15 January 2017 (UTC)

To make it more complicated, I only want those awards that have been added yesterday or have been added since the last time the query ran. - this isn't possible with SPARQL. The best what you could get is time, when the item was last edited. --Edgars2007 (talk) 15:32, 15 January 2017 (UTC)