View source for Testwiki:Request a query/Archive/2019/08

{{Archive}}

== Border cities of a country ==

What would be the query to get a list of the cities of a country A that are X km from another country B or the border cities of a given country (such as {{Q|52585}} or {{Q|55378}} in {{Q|414}})? --[[Special:Contributions/190.226.77.162|190.226.77.162]] 06:39, 30 July 2019 (UTC)
:Hi. You might try this one
:{{Sparql|query=#defaultView:Map
SELECT ?border_city ?border_cityLabel ?countryLabel
(GROUP_CONCAT(DISTINCT ?natureLabel; SEPARATOR = "; ") AS ?nature) ?coords 
WHERE {
    ?border_city wdt:P31 wd:Q902814.
  ?border_city wdt:P31 ?nature. 
    OPTIONAL { ?border_city wdt:P17 ?country. }
  OPTIONAL { ?border_city wdt:P625 ?coords. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                           ?nature rdfs:label ?natureLabel . 
                         ?border_city rdfs:label ?border_cityLabel.
                         ?country  rdfs:label ?countryLabel.}
}
GROUP BY ?border_city ?border_cityLabel ?countryLabel ?coords
order by ?countryLabel ?border_cityLabel}} [[User:Bouzinac|Bouzinac]] ([[User talk:Bouzinac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 11:10, 30 July 2019 (UTC)
::Thanks, it was just what I needed. After some adjustments I stay like this:
::{{Sparql|query=#defaultView:Map
SELECT ?border_city ?border_cityLabel ?countryLabel ?coords 
WHERE {
  ?border_city wdt:P31 wd:Q902814.
  OPTIONAL { ?border_city wdt:P17 ?country. }
  OPTIONAL { ?border_city wdt:P625 ?coords. }
  # Countries in South America
  FILTER (?country IN (wd:Q77, wd:Q414, wd:Q298, wd:Q750, wd:Q419, wd:Q717, wd:Q736, wd:Q739, wd:Q733, wd:Q155))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                         ?border_city rdfs:label ?border_cityLabel.
                         ?country  rdfs:label ?countryLabel.}
}
GROUP BY ?border_city ?border_cityLabel ?countryLabel ?coords
order by ?countryLabel ?border_cityLabel}}
::What I was interested in was getting the border cities of {{Q|18}} (except {{Q|734}}, {{Q|730}} and {{Q|3769}})  so I'm pretty happy with the result. --[[Special:Contributions/190.226.77.162|190.226.77.162]] 13:59, 30 July 2019 (UTC)
:::Note that there is a lack of data : not every city on a border has been flagged {{Q|Q902814}}. So if you see some of them, please add {{Q|Q902814}} on their wikidatas page. [[User:Bouzinac|Bouzinac]] ([[User talk:Bouzinac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 14:26, 30 July 2019 (UTC)
::::I was adding that property to some South American cities that I saw in [[w:Border town#In South America|1]] and [[w:es:Ciudad fronteriza#En Sudamérica|2]] and others may also be interested in marking other cities around the world so I took the liberty of adding the query in the [[Wikidata:SPARQL_query_service/queries/examples#Border cities of the world|examples]]. With regard to the query I was working with, I noted the following inconsistencies:
::::* {{Q|1323834}}, {{Q|2602785}} and {{Q|1023521}} have two coordinates in {{P|625}}.
::::* {{Q|Q328303}}, {{Q|272795}}, {{Q|2295357}}, {{Q|1059928}}, {{Q|2196660}} and {{Q|22064419}} are not border cities.
::::--[[Special:Contributions/190.31.204.96|190.31.204.96]] 13:27, 31 July 2019 (UTC)
:::::Solved some obvious inconsistencies. I am curious : what is your definition of border town? I was in "conflict" with {{u|Hjart}} about {{Q|Q3162907}} : he states his town wouldn't be called a border town, although it lies on the border Denmark/Germany.
:::::*Would it be any cities whose shapes are touching another country (my preferred definition)?
:::::*Or would it be any cities/town having its downtown/buildings very much touching another country ?
:::::*Or any cities that have a means of transport between countries? Example : town A ==road==town B >>borders cities. But what if a mountain/river separates A and B, and no direct means of transport?  [[User:Bouzinac|Bouzinac]] ([[User talk:Bouzinac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 14:43, 31 July 2019 (UTC)
::::::Please note that {{Q|Q3162907}} is not a city or town, but a large predominantly rural area and as such cannot fit the definition "border town". The main town {{Q|3223979}} maybe could be called a "border town", since it's situated just a few kilometers north of the border, but IMHO isn't really a traditional border town in the same way i.e. {{Q|707345}} is. --[[User:Hjart|Hjart]] ([[User talk:Hjart|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:24, 31 July 2019 (UTC)
: I've asked a similar question some time ago: "what cities (or towns, or other populated places) share boundaries?" (I can't find it in archives because of inexact wording). But it requires checking of <math>O(n^2)</math> linkings which always timeouts. I wonder if someone can propose more effective solution. --[[User:Infovarius|Infovarius]] ([[User talk:Infovarius|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:06, 1 August 2019 (UTC)

== Federated query with lei.info ==

Hi,

We have many LEI code in Wikidata database. I request a query on this identifier.

On webpage https://lei.info/sparql , i construct a query for having examples of real LEI code from database lei.info : 
{{code|
SELECT ?glei ?legalName ?identifiedBy ?leiCode ?legalEntityStatus
WHERE {
  ?glei voc:legalName ?legalName .
  ?glei voc:legalAddress ?legalAddress .
  ?glei voc:identifiedBy ?identifiedBy.
  ?identifiedBy voc:leiCode ?leiCode.
  ?glei voc:legalEntityStatus ?legalEntityStatus.
  ?legalAddress voc:country "KY" . #Cayman Islands
}
}}

I need a federated query with wikidata database, for check some LEI codes. --[[User:Manu1400|Manu1400]] ([[User talk:Manu1400|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:59, 1 August 2019 (UTC)
: {{support}} LEI.INFO uses AllegroGraph RDF triple store and SPARQL 1.1 query engine. [https://lei.info/portal/resources/semantic-lei-data/ Source] [[User:Manu1400|Manu1400]] ([[User talk:Manu1400|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 17:22, 1 August 2019 (UTC)
::Are you sure that the LEI SPARQL endpoint supports federated queries, and Wikidata in particular? I don't get anything federated here. We cannot use WDQS, since lei.info is not a supported endpoint for federation according to [[:mw:Wikidata Query Service/User Manual/SPARQL Federation endpoints]]. —[[User:MisterSynergy|MisterSynergy]] ([[User talk:MisterSynergy|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:42, 1 August 2019 (UTC)
::: "AllegroGraph supports queries with distributed databases. You can group multiple triple-stores, both local and remote into a single virtual store. It allows thread-safe opening of multiple triple-databases from one application (for the read only parts of the database). Queries over multiple databases are easy with direct data access from applications. It also supports physical merging of databases." Source: from paragraph [https://franz.com/agraph/allegrograph/ GeoTemporal Reasoning, Social Network Analysis, and Additional Features] [[User:Manu1400|Manu1400]] ([[User talk:Manu1400|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 19:41, 1 August 2019 (UTC)
::::Not sure whether this necessarily means that you can actually use this feature. Usually I'd say that something like this should work via their endpoint:
::::<syntaxhighlight lang="sparql">PREFIX voc: <http://lei.info/voc/l1/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>

SELECT ?glei ?leiCode ?legalName ?legalEntityStatus ?wdItem WHERE {
  SERVICE <https://query.wikidata.org/bigdata/namespace/wdq/sparql> {
    ?wdItem wdt:P1278 ?leiCode .
    FILTER(ISLITERAL(?leiCode)) . # to ignore unknown values
  }
  ?glei voc:identifiedBy/voc:leiCode ?leiCode .
  ?glei voc:legalAddress/voc:country 'KY' . # Cayman Islands
  ?glei voc:legalName ?legalName .
  ?glei voc:legalEntityStatus ?legalEntityStatus .
}</syntaxhighlight>
::::However, it does not, and it does not tell me why. —[[User:MisterSynergy|MisterSynergy]] ([[User talk:MisterSynergy|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 19:55, 1 August 2019 (UTC)
== bind, coalesce or concat ??? ==

{{SPARQL|query=
SELECT ?item ?date ?prec ?cal WHERE {
  ?item p:P569/psv:P569 [
    wikibase:timeValue ?date;
    wikibase:timePrecision ?prec;
    wikibase:timeCalendarModel ?cal
  ].
  ?item wdt:P27 wd:Q1005.
}
}}

Hi, I want to get the values of ?date, ?prec and ?cal in one column cell separated by a comma. And if there is no {{P|P569}} in the item, then I want to get a literary "empty" into the cell (e.g. in {{Q|Q65660832}}). Can you help me, please? [[User:Doc Taxon|Doc Taxon]] ([[User talk:Doc Taxon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:57, 1 August 2019 (UTC)

:{{ping|Doc Taxon}}
:{{SPARQL|query=SELECT ?item ?date_prec_cal WHERE {
  ?item wdt:P27 wd:Q1005.
  OPTIONAL { ?item p:P569/psv:P569 [
    wikibase:timeValue ?date;
    wikibase:timePrecision ?prec;
    wikibase:timeCalendarModel ?cal
  ].
  }
  BIND(IF(BOUND(?date),CONCAT(STRBEFORE(STR(?date),'T'),',',STR(?prec),',',STRAFTER(STR(?cal),'entity/')),'empty') AS ?date_prec_cal)
}
ORDER BY ?date_prec_cal
}}
:--[[User:Larske|Larske]] ([[User talk:Larske|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 03:17, 2 August 2019 (UTC)
::wow, cool, thanks! [[User:Doc Taxon|Doc Taxon]] ([[User talk:Doc Taxon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 07:29, 2 August 2019 (UTC)

== Candidacies by electoral district ==

I would like to request the following query: {{P|P3602}} {{Q|Q25226840}} with the following qualifiers: {{P|P768}} {{Q|Q24932380}} and {{P|P1268}} {{Q|Q1393123}}. --[[User:Davidpar|Davidpar]] ([[User talk:Davidpar|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:16, 1 August 2019 (UTC)

:{{ping|Davidpar}} this, probably:
:{{SPARQL|query=SELECT ?item ?itemLabel 
WHERE 
{
  ?item p:P3602 [ps:P3602 wd:Q25226840 ; pq:P768 wd:Q24932380 ; pq:P1268 wd:Q1393123  ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es". }
}
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:24, 1 August 2019 (UTC)

::{{ping|Tagishsimon}} Thanks! It is possible to show column with {{P|P3602}}/{{Q|P1545}}? --[[User:Davidpar|Davidpar]] ([[User talk:Davidpar|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 06:47, 2 August 2019 (UTC)


::{{ping|Davidpar}} Seems so:
::{{SPARQL|query=SELECT ?item ?itemLabel ?ordinal
WHERE 
{
  ?item p:P3602 [ps:P3602 wd:Q25226840 ; pq:P768 wd:Q24932380 ; pq:P1268 wd:Q1393123 ; pq:P1545 ?ordinal] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],es". }
}
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:59, 2 August 2019 (UTC)
== Searching for all the Wikidata items with descriptions both in Basque and Spanish ==

I would like to collect the items in Wikidata with descriptions both in Basque and Spanish languages. I would like to use those bilingual descriptions to improve a Machine Translation system specialised in translating such descriptions. I get some results when I make a restricted query, for example, I get 144 items when I restrict the search to people born in Bilbao (https://w.wiki/6Wh). But if the query is more general, and asks ,for example, for humans then it happens an "time out" error. 

{{SPARQL|query=#Search for items with descriptions both in Basque and Spanish.
#Here only people born in Bilbao
SELECT ?item ?genderLabel ?occupationLabel ?domainLabel ?positionLabel ?itemDescription ?itemLabel WHERE {
  ?item wdt:P31 wd:Q5.
  OPTIONAL { ?item wdt:P21 ?gender. }
  OPTIONAL { ?item wdt:P106 ?occupation. }
  OPTIONAL { ?item wdt:P101 ?domain. }
  OPTIONAL { ?item wdt:P39 ?position. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "eu, es". }
  FILTER(EXISTS {
    ?item schema:description ?itemdesc, ?itemdesc2.    FILTER(((LANG(?itemdesc)) = "eu") && ((LANG(?itemdesc2)) = "es"))
  })
  
  ?item wdt:P19 wd:Q8692.
}
ORDER BY (?itemLabel)

}}
Can anyone help me to collect so a list of bilingual descriptions? Have I to download the whole Wikidata Dump? Thanks.[[User:Ksarasola|Ksarasola]] ([[User talk:Ksarasola|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:34, 25 July 2019 (UTC)
*I can't help you directly with your question, but maybe this is interesting: [[User:Pasleim/Language_statistics_for_items]] has some statistics. For eu, there are 7.6 million descriptions. Obviously, it might just be "Wikimediako argipen orri". --- [[User talk:Jura1|Jura]] 12:54, 2 6 July 2019 (UTC)
**You are right, {{ping|Jura1}} That is not what I looking for. But thanks for your answer, your information could be interesting to know the number of descriptions in Basque and Spanish. 7,623,021 (13.4%) descriptions in Basque and 30,584,776 (53.8%) in Spanish. [[User:Ksarasola|Ksarasola]] ([[User talk:Ksarasola|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:14, 27 July 2019 (UTC)

:::{{Ping|Jura1|Ksarasola}} Date of Birth is set to the Country, so I think we need to set it to anywhere in the country using:

:::  UNION  {  ?item wdt:P20/wdt:P131* wd:Q3995 }      
:::  UNION  {  ?item wdt:P937/wdt:P131* wd:Q3995 } 

:::It also looks like the descriptions are of people only.
::: PS I think you taught me that one! [[User:Llywelyn2000|Llywelyn2000]] ([[User talk:Llywelyn2000|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 15:59, 29 July 2019 (UTC)

::::{{Ping|Llywelyn2000}} The relation wdt:P19 is not 'Date of Birth', it is 'Place of Birth'. So I think the query is well formed. However, that query is not what I want. My goal is to collect all the concepts (or as many as possible) with description both in Basque and Spanish, whatever they are persons or not. But my problem is that when I remove this restriction (Born in Bilbao), even though I am still asking for humans, as the number of concepts is too big the query finishes with "time out". And I have no results. However what I want is to collect bilingual descriptions not only for humans, but for any comcepts. But in that case the problem will be bigger; if the nunber for humans is too big, the number of concepts will be even bigger. I think I should work with the Wikidata dump, I know it will be more complex, but it seems that it is the only way to get what I want. Thanks anyway.[[User:Ksarasola|Ksarasola]] ([[User talk:Ksarasola|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 23:35, 29 July 2019 (UTC)
:::::{{Ping|Ksarasola}} I meant Place of Birth, Froydian slip! Sorry. I agree that working on the datadump is the way forward. All the best! [[User:Llywelyn2000|Llywelyn2000]] ([[User talk:Llywelyn2000|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 03:55, 30 July 2019 (UTC)

Small step: Using the following query I get 840 items that are instance-of human and that have description both in Basque and Spanish:
{{SPARQL|query=# Labels and descriptions of humans that have an article and description both in the Basque and Spanish Wikipedia
SELECT DISTINCT ?item ?esLabel ?euLabel ?itemdesc ?itemdesc2 WHERE {
  ?item wdt:P31 wd:Q5.
  ?weu schema:about ?item;
    schema:inLanguage "eu".
  ?wes schema:about ?item;
    schema:inLanguage "es".
  ?item rdfs:label ?esLabel.
  FILTER((LANG(?esLabel)) = "es")
  ?item schema:description ?itemdesc.
  FILTER((LANG(?itemdesc)) = "es")
  ?item rdfs:label ?euLabel.
  FILTER((LANG(?euLabel)) = "eu")
  ?item schema:description ?itemdesc2.
  FILTER((LANG(?itemdesc2)) = "eu")
}
}}
However what I want is to collect bilingual descriptions not only for humans, but for all items in Wikidata. And when I remove "?item wdt:P31 wd:Q5."  I get a 'Query timeout limit reached' message.

I download the wikidata dump. (latest-all.json.bz2 from  https://dumps.wikimedia.org/wikidatawiki/entities/ )
But its is 36Gb and I can not use it in my laptop.[[User:Ksarasola|Ksarasola]] ([[User talk:Ksarasola|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:26, 30 July 2019 (UTC)

: {{reply to|Ksarasola}} searching for labels or descriptions by language is not very efficient in the query service. I think the regular search is actually a much better fit for this, because it has the [[:mw:Help:WikibaseCirrusSearch#hasdescription|hasdescription]] keyword: see [[Special:Search/hasdescription:es hasdescription:eu]]. If you’re familiar with the API, you can download up to 5000 results at a time ([[Special:ApiSandbox#action=query&format=json&list=search&srsearch=hasdescription%3Aes+hasdescription%3Aeu&srlimit=max&srinfo=totalhits&srprop=|API sandbox]]) and follow continuation until you have all the search results; several API client libraries can take care of the continuation automatically (e. g. [https://github.com/mediawiki-utilities/python-mwapi#query-with-continuation python-mwapi]). --[[User:TweetsFactsAndQueries|TweetsFactsAndQueries]] ([[User talk:TweetsFactsAndQueries|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:34, 7 August 2019 (UTC)

== Chinese writers whose copyright expired ==

Hi! I would like to know how to query a group like the title. I think the logic could be like this:
#they should have instance of = humans, occupation = writer.
#they should have native lang = zh/cmn/yue/wuu/nan/hak/gan/cdo, or lang spoken, written or signed = zh/zh-xx(all the varieties)/cmn/yue/wuu/nan/hak/gan/cdo/lzh.
#to determine copyright expiration, I thought of three ways:
##those who died before a given year, e.g. 1948
##those who died in a given year (for example for those whose copyright expire in 2031, their year of death would be 1960).
##those who were born before a given year. (In case date of death is missing in wikidata, which is quite a common problem for Chinese people's items. Writers born 110+70=180 years ago definitely have copyright expired.)
Thanks in advance!--[[User:Roy17|Roy17]] ([[User talk:Roy17|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:04, 2 August 2019 (UTC)

:{{ping|Roy17}} something like that?
:{{SPARQL|query=
SELECT * WHERE {
  ?q wdt:P106 wd:Q36180 ; wdt:P31 wd:Q5 . #writer
  OPTIONAL { ?q wdt:P570 ?dod . FILTER ( year(?dod) < 1948 ) } #death before 1948
  OPTIONAL { ?q wdt:P569 ?dob . FILTER (year(?dob) < 1840) } #birth before 1840
  { ?q wdt:P103/wdt:P279* wd:Q7850 } UNION { ?q wdt:P1412/wdt:P279* wd:Q7850 }
}
}}
:Cheers, [[User:VIGNERON|VIGNERON]] ([[User talk:VIGNERON|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 09:13, 6 August 2019 (UTC)
::{{re|VIGNERON}} your codes were great, but a column of names is most important for making a list, so I tried adapting it but I couldnt figure out how to call labels. I call p1559 and p18 instead.
::{{SPARQL|query=SELECT * WHERE {
  ?q wdt:P106 wd:Q36180 ; wdt:P31 wd:Q5 . #writer
  OPTIONAL { ?q wdt:P570 ?dod . FILTER ( year(?dod) < 1948 ) } #death before 1948
  OPTIONAL { ?q wdt:P569 ?dob . FILTER (year(?dob) < 1840) } #birth before 1840
  OPTIONAL { ?q wdt:P1559 ?name}
  OPTIONAL { ?q wdt:P18 ?photo}
  { ?q wdt:P103/wdt:P279* wd:Q7850 } UNION { ?q wdt:P1412/wdt:P279* wd:Q7850 }
}
ORDER BY DESC(?dod)
}}
::Or
::{{SPARQL|query=SELECT ?q ?qLabel ?dod ?dob
WHERE {
  ?q wdt:P106 wd:Q36180 ; wdt:P31 wd:Q5 . #writer
  OPTIONAL { ?q wdt:P570 ?dod . FILTER ( year(?dod) < 1948 ) } #death before 1948
  OPTIONAL { ?q wdt:P569 ?dob . FILTER (year(?dob) < 1840) } #birth before 1840
  { ?q wdt:P103/wdt:P279* wd:Q7850 } UNION { ?q wdt:P1412/wdt:P279* wd:Q7850 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "zh,[AUTO_LANGUAGE],en" }
}
ORDER BY DESC(?dod)
}}--[[User:Roy17|Roy17]] ([[User talk:Roy17|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:58, 9 August 2019 (UTC)

== Getting QIDs from a en:Wikipedia category page? ==

Is there a query that will list all the items on this wikipedia article, https://en.wikipedia.org/wiki/Category:United_States_geologic_formation_stubs ? Ultimately, I'd like to find their {{P|131}} and {{P|6202}}, too. Thanks. [[User:Trilotat|Trilotat]] ([[User talk:Trilotat|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 17:23, 5 August 2019 (UTC)
: Not that easy task. For looking up items for articles in a category, I would recommend you [[petscan:|PetScan]] (right now it seems out of order though). But it cannot look up additional data, like P131. Queries can do this but not for subcategories (unless done individually), like this:
: {{SPARQL|query=SELECT ?title ?article ?item ?location ?geolex WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam wikibase:endpoint "en.wikipedia.org" .
    bd:serviceParam mwapi:gcmtitle "Category:United States geologic formation stubs" .
    bd:serviceParam mwapi:generator "categorymembers" .
    bd:serviceParam mwapi:gcmprop "title" .
    bd:serviceParam mwapi:gcmtype "page" .
    bd:serviceParam mwapi:gcmlimit "max" .
    ?title wikibase:apiOutput mwapi:title .
    ?item wikibase:apiOutputItem mwapi:item .
  } .
  ?article schema:about ?item; schema:isPartOf <https://en.wikipedia.org/> .
  OPTIONAL { ?item wdt:P131 ?location } .
  OPTIONAL { ?item wdt:P6202 ?geolex } .
} }}
: --[[User:Matěj Suchánek|Matěj Suchánek]] ([[User talk:Matěj Suchánek|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 08:01, 6 August 2019 (UTC)
::You can generate a PagePile with PetScan and then use the input directly for TABernacle. I did not try it, but I don't see why it should not work. [[User:Steak|Steak]] ([[User talk:Steak|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 09:30, 9 August 2019 (UTC)

== Chess players without description ==

Hi, I am looking for all chess player items without a german description. I have looked at [[Wikidata:SPARQL_query_service/queries/examples#Actresses_without_a_description_in_Spanish|some examples]] and came up with this query, but it seems to be wrong, because it gives zero results althouth there are target items. Can somebody fix?
:{{SPARQL|query=SELECT DISTINCT ?item ?itemLabel
WHERE
{
	?item wdt:P106 wd:Q10873124 .
	OPTIONAL { ?item schema:description ?itemDescription . FILTER(lang(?itemDescription)="de") }
	FILTER (!BOUND(?itemDescription))
	SERVICE wikibase:label { bd:serviceParam wikibase:language "de"}
}
}}
-- [[User:Steak|Steak]] ([[User talk:Steak|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:31, 9 August 2019 (UTC)
*fixed above. --- [[User talk:Jura1|Jura]] 18:33, 9 August 2019 (UTC)
::Thanks! [[User:Steak|Steak]] ([[User talk:Steak|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:43, 9 August 2019 (UTC)
== Select time in two statements with P577 ==

Hello. Let a variable ?p in a specific query contain elements that have exactly two statements with {{P|577}} (analogously [[Wikidata:Request_a_query/Archive/2019/02#Get_count_of_statements_for_specified_property|this]]).

The query should select items for which:
:1) in both statements the year and month are the same;
:2) for one of statements, ''precision''=10 (months), and for the other statement, ''precision''=11 (days).
The query should output an selected element and time+precision for statement with precision=10 in form (example for Q65683198):
  Q65683198    2018-02-01T00:00:00Z/10
Thanks. --[[User:Renamerr|Renamerr]] ([[User talk:Renamerr|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:05, 4 August 2019 (UTC)
::{{SPARQL|query=SELECT ?p (CONCAT(STR(?value), '/10') AS ?date) WHERE {
  VALUES ?p {} .
  ?p p:P577 ?statement1, ?statement2 .
  FILTER( ?statement1 != ?statement2 ) .
  ?statement1 psv:P577 [ wikibase:timePrecision 10; wikibase:timeValue ?value ] .
  ?statement2 psv:P577 [ wikibase:timePrecision 11 ] .
} }}
:: --[[User:Matěj Suchánek|Matěj Suchánek]] ([[User talk:Matěj Suchánek|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 08:13, 6 August 2019 (UTC)

::::Thank you very much {{ping|Matěj Suchánek}}. I modified this version of the query to select the same month and year. This can be tested for <code>VALUES ?p { wd:Q23916181 } .</code>
::::{{SPARQL|query=SELECT ?p (CONCAT(STR(?value), '/10') AS ?date) WHERE {
  VALUES ?p {} .
  ?p p:P577 ?statement1, ?statement2 .
  FILTER( ?statement1 != ?statement2 ) .
  ?statement1 psv:P577 [ wikibase:timePrecision 10; wikibase:timeValue ?value ] .
  ?statement2 psv:P577 [ wikibase:timePrecision 11; wikibase:timeValue ?value2 ] .
  FILTER( SUBSTR(STR(?value), 1, 7) = SUBSTR(STR(?value2), 1, 7)) .
} }}
::::Yours sincerely, --[[User:Renamerr|Renamerr]] ([[User talk:Renamerr|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 11:26, 11 August 2019 (UTC)

== Identifiers used in multiple Wikidata items ==

I would like to find all instances where a NAID ({{P|1225}}) value is reused on more than one Wikidata item. I know how to get a list of all items with P1225 and their values, but I haven't been able to figure out if there is a way to make a query such that only the items whose value appears more than once across all Wikidata items are returned. Basically, NAID is a unique identifier, so I'd like to check for anomalies (possible duplicates) if multiple items have a same identifier. Thanks! [[User:Dominic|Dominic]] ([[User talk:Dominic|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:51, 8 August 2019 (UTC)
:{{ping|Dominic}} Here's a way to get pairs of items with the same NAID: {{SPARQL|query=SELECT ?item1 ?item1Label ?item2 ?item2Label ?code {
  ?item1 wdt:P1225 ?code.
  ?item2 wdt:P1225 ?code.
  FILTER(STR(?item1) < STR(?item2))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}} Hope that helps. [[User:Mahir256|Mahir256]] ([[User talk:Mahir256|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:55, 8 August 2019 (UTC)

::You can also have a look at [[Wikidata:Database reports/Constraint violations/P1225#"Unique value" violations]] :-) —[[User:MisterSynergy|MisterSynergy]] ([[User talk:MisterSynergy|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:58, 8 August 2019 (UTC)

:::Nice, thank you both! [[User:Dominic|Dominic]] ([[User talk:Dominic|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 19:06, 8 August 2019 (UTC)
::[[User:Dominic|Dominic]]: The property talk page also includes links to queries for this (next to the constraint display). --- [[User talk:Jura1|Jura]] 11:37, 10 August 2019 (UTC)

== P304 of a reference ==

Hello. I want to find all items with {{P|31}} -> {{Q|5}} that have a reference with {{P|854}} -> http://www.parliament.cy/images/media/assetfile/VIVLIO%2050HRONA.pdf. I want in the results to have item label and {{P|304}} of that reference. [[User:Xaris333|Xaris333]] ([[User talk:Xaris333|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 00:43, 11 August 2019 (UTC)
:{{SPARQL|query=SELECT ?item ?itemLabel ?property ?page WHERE {
  ?refHandle pr:P854 <http://www.parliament.cy/images/media/assetfile/VIVLIO%2050HRONA.pdf> .
  OPTIONAL { ?refHandle pr:P304 ?page }
  ?item ?property [ prov:wasDerivedFrom ?refHandle ]; wdt:P31 wd:Q5 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
}}&nbsp;If you are not interested in the properties of the statements which have this reference, [//query.wikidata.org/#SELECT%20DISTINCT%20%3Fitem%20%3FitemLabel%20%3Fpage%20WHERE%20%7B%0A%20%20%3FrefHandle%20pr%3AP854%20%3Chttp%3A%2F%2Fwww.parliament.cy%2Fimages%2Fmedia%2Fassetfile%2FVIVLIO%252050HRONA.pdf%3E%20.%0A%20%20OPTIONAL%20%7B%20%3FrefHandle%20pr%3AP304%20%3Fpage%20%7D%0A%20%20%3Fitem%20%3Fproperty%20%5B%20prov%3AwasDerivedFrom%20%3FrefHandle%20%5D%3B%20wdt%3AP31%20wd%3AQ5%20.%0A%20%20SERVICE%20wikibase%3Alabel%20%7B%20bd%3AserviceParam%20wikibase%3Alanguage%20%27en%27%20%7D%0A%7D%20ORDER%20BY%20ASC%28%3FitemLabel%29 this query] could also be interesting for you (basic mapping "person -> page"). —[[User:MisterSynergy|MisterSynergy]] ([[User talk:MisterSynergy|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 07:10, 11 August 2019 (UTC)

Thanks! [[User:Xaris333|Xaris333]] ([[User talk:Xaris333|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:52, 11 August 2019 (UTC)

== References with implicit URL ==

Would it be possible to query for (a) all statements which have a reference which only includes {{P|P813}} and no other properties, (b) all statements which have a reference which contains only one property–value pair, and (c) all statements which use {{P|P813}} as a qualifier without {{P|P854}}? (This is for [[Wikidata:Project chat#P813 as qualifier]].) [[User:Jc86035|Jc86035]] ([[User talk:Jc86035|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 11:44, 11 August 2019 (UTC)
== Female Swedish writers of the 20th century, who were born in Stockholm ==

How to write a query for it. I have started, but something is not working well.

{{SPARQL5|query=SELECT DISTINCT ?item ?itemLabel ?itemDescription ?gender ?genderLabel ?pob ?pobLabel
WHERE
{
  ?item wdt:P19 / wdt:P276* / wdt:P131* wd:Q1754.
  ?item wdt:P106 wd:Q36180.
  ?item wdt:P27 wd:Q34.
  VALUES ?gender { wd:Q6581072 }.
  ?item wdt:P21 ?gender .
  { ?item wdt:P570 ?dod . FILTER( YEAR(?dod) > 1900 ) } 
  UNION { ?item wdt:P569 ?dob . FILTER( YEAR(?dob) > 1900 && YEAR(?dob) < 2001 ) } 
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P19 ?pob .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],sv,en". }
}
LIMIT 1000
}}

--[[User:Juandev|Juandev]] ([[User talk:Juandev|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 01:44, 12 August 2019 (UTC)

:I edited it above. --- [[User talk:Jura1|Jura]] 02:23, 12 August 2019 (UTC)

== A query about genre books ==

Hello!
I would like to make a query to find all books with some genre and write by women. I have create this one but it doesn´t work.
Can you help me?

{{SPARQL|query=SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P136 wd:Q26928598;
  wdt:P136 wd:Q7252;
  wdt:P136 wd:Q12132683 .
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es". }
  }
}}
{{unsigned|Lmerice|15 augusti 2019 kl. 07.59‎ (UTC)}}

:I have removed a space before Q1213268 in your query above. Now it returns one item.
:--[[User:Larske|Larske]] ([[User talk:Larske|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 08:23, 15 August 2019 (UTC)
::
::Your query ask for items who have 3 genres (distopia, feminism and science-fiction). As Larske said, there's only one result for this.<br />This query give you all distopian books and subclasses of book who are written by a female. Il you want another genre, just replace Q26928598 by the other one you want : {{SPARQL|query=SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q571 ; #books and subclasses of book
        wdt:P136 wd:Q26928598 ; # distopia genre 
        wdt:P50 ?author . #author of the book
  ?author wdt:P21 wd:Q6581072 . #author is female
#  wdt:P136 wd:Q7252;
#  wdt:P136 wd:Q12132683 .
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es". }
  }
}}<br />If you want all the books from these 3 genres, you must put a union between all of them<br />{{SPARQL|query=SELECT distinct ?itemLabel ?authorLabel ?genreLabel ?item
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q571 ; #books and subclasses of book
        wdt:P136 ?genre ; #genre of the book
        wdt:P50 ?author . #author of the book
  ?author wdt:P21 wd:Q6581072 . #author is female
  {{?item wdt:P136 wd:Q26928598 .} union {?item wdt:P136 wd:Q7252 .} union {?item wdt:P136 wd:Q12132683 .}} #book is a science-fiction novel, a distopia or about feminism
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,es". }
  }
}}<br />[[User:Simon Villeneuve|Simon Villeneuve]] ([[User talk:Simon Villeneuve|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 11:41, 15 August 2019 (UTC)
== Railway stations in Ireland ==

I’m trying to create a list of railway stations in the Republic of Ireland, listed in English alphabetical order, with their Irish name equivalent next to it. I tried adapting examples but I didn’t succeed. Thank you! --[[User:Metrophil|Metrophil]] ([[User talk:Metrophil|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:05, 10 August 2019 (UTC)

:{{ping|Metrophil}} This lists all items in the railway station subclass tree, where country=Ireland.
:{{SPARQL|query=SELECT DISTINCT ?item ?itemLabel_en ?itemLabel_ga
WHERE
{
  ?item wdt:P31/wdt:P279* wd:Q55488 . 
  ?item wdt:P17 wd:Q27 .
  OPTIONAL { ?item rdfs:label ?itemLabel_en . FILTER(lang(?itemLabel_en)="en") }
  OPTIONAL { ?item rdfs:label ?itemLabel_ga . FILTER(lang(?itemLabel_ga)="ga") }
} order by ?itemLabel_en
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:32, 10 August 2019 (UTC)
:Hi [[User:Tagishsimon|Tagishsimon]], thank you and apologies for replying so late! Really helpful --[[User:Metrophil|Metrophil]] ([[User talk:Metrophil|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:49, 16 August 2019 (UTC)

== Example of query where an item does NOT have a specified property ==

I'm not sure how to build this. I'd like to search, for example, for {{Q|Q13442814}}, {{P|1433}} in {{Q|Q53953275}}, without {{P|50}} or {{P|2093}}. Can you point to one I can edit to look like that or might someone be so kind? Thanks. [[User:Trilotat|Trilotat]] ([[User talk:Trilotat|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:57, 15 August 2019 (UTC)

:{{ping|Trilotat}} Two approaches, fwiw.

:{{SPARQL|query=SELECT ?item ?itemLabel 
WHERE
{
  ?item wdt:P1433 wd:Q53953275 .
  ?item wdt:P31 wd:Q13442814 .
  optional { ?item wdt:P50 ?author . }
  optional { ?item wdt:P2093 ?authorstring . }
  filter(!bound(?author) && !bound(?authorstring) )
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
}} 

:{{SPARQL|query=SELECT ?item ?itemLabel
WHERE
{
  ?item wdt:P1433 wd:Q53953275 .
  ?item wdt:P31 wd:Q13442814 .
  filter not exists { ?item wdt:P50 [] . }
  filter not exists  { ?item wdt:P2093 [] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:04, 15 August 2019 (UTC)
::{{Ping|Tagishsimon}} Thanks! That's so very helpful. [[User:Trilotat|Trilotat]] ([[User talk:Trilotat|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 01:39, 16 August 2019 (UTC)
== Select items where a property starts by xx ==

Hi, I need all the {{Q|Q33146843}} where its {{P|4335}} has 43 in the two first digits of the code. Thanks, [[User:Amadalvarez|Amadalvarez]] ([[User talk:Amadalvarez|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 05:29, 16 August 2019 (UTC)

:{{ping|Amadalvarez}} 
:{{SPARQL|query=SELECT ?item ?itemLabel (?p4335 AS ?IDESCAT_code) {
  ?item wdt:P31 wd:Q33146843 .
  ?item wdt:P4335 ?p4335 .
  FILTER(STRSTARTS(?p4335,'43'))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?p4335
}}
:--[[User:Larske|Larske]] ([[User talk:Larske|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 07:18, 16 August 2019 (UTC)
::Thanks, {{u'|Larske}}. I know for next time. [[User:Amadalvarez|Amadalvarez]] ([[User talk:Amadalvarez|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 06:16, 17 August 2019 (UTC)

== List instances of multiple instances of same qualifier per property ==

I've been able to write this query:

:{{SPARQL|query=select ?player ?playerLabel ?event (COUNT(?team) as ?teams) 
WHERE {
  ?player  wdt:P106 wd:Q937857.
  ?player p:P1344  ?event. 
?event ps:P1344 wd:Q28937555 ;  #how to change so that I can list all instances, not just 1 individual
        pq:P54 ?team ;
         OPTIONAL { ?event pq:P1350 ?matches;}
         OPTIONAL { ?event pq:P1351 ?goals ;}
service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }  
}
GROUP BY ?player ?playerLabel ?event ?teams HAVING ( ?teams > 1 )
ORDER BY ?teamLabel ?playerLabel
}}
:--[
which finds cases where for a particular P1344 there are multiple entries in the P54  member of sports team. So for instance for this item https://www.wikidata.org/wiki/Q22342392 under 2017-18 Bundesliga, there are 2 entries under member of sports team (P54). What I can not figure out is how to run the query across the dataset, not just limited to a single P1344. I recognize I may need to limit the result set, but I'm not able to get the syntax right. Thanks

:{{ping|CanadianCodhead}} Probably this sort of direction. Your ?event was a statement, not a value. hth.
:{{SPARQL|query=select ?player ?playerLabel ?event ?eventLabel (COUNT(?team) as ?teams) 
WHERE {
  ?player  wdt:P106 wd:Q937857.
  ?player p:P1344  ?statement. 
  ?statement ps:P1344 ?event ;  #how to change so that I can list all instances, not just 1 individual
        pq:P54 ?team ;
         OPTIONAL { ?event pq:P1350 ?matches;}
         OPTIONAL { ?event pq:P1351 ?goals ;}
service wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }  
}
GROUP BY ?player ?playerLabel ?event ?eventLabel ?teams  HAVING ( ?teams > 1 )
ORDER BY ?teamLabel ?playerLabel 
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 03:33, 17 August 2019 (UTC)
== Avoiding duplicates ==

I've made this query to get all women whose occupation is either "director" or "film director" or "television director" or "anime director" and have an Anime News Network identifier ([[p:P1982]]), and I want to see their occupation(s), and their dates of birth and death when they have one, but how can I make people with more than one occupation appear only once? I've tried with GROUP_CONCAT, GROUP BY etc. but it says "bad aggregate"

{{SPARQL|query=
SELECT ?item ?itemLabel ?occupationLabel ?dateofbirth ?dateofdeath (URI(CONCAT("https://www.animenewsnetwork.com/encyclopedia/people.php?id=",?ANNid)) AS ?ANN)
WHERE {
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P21 wd:Q6581072 .
  ?item wdt:P1982 ?ANNid .
  { ?item wdt:P106 wd:Q3455803 } UNION { ?item wdt:P106 wd:Q2059704 } UNION { ?item wdt:P106 wd:Q11284414 } UNION { ?item wdt:P106 wd:Q2526255 }
  ?item wdt:P106 ?occupation
  OPTIONAL { ?item wdt:P569 ?dateofbirth . }
  OPTIONAL { ?item wdt:P570 ?dateofdeath . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,ja" }
}
}}
--[[User:Lombres|Lombres]] ([[User talk:Lombres|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:28, 18 August 2019 (UTC)

:{{ping|Lombres}} Several things to do; not least, cause the label values to exist so they can be aggregated. hth.
:{{SPARQL|query=SELECT ?item ?itemLabel (group_concat(distinct ?occupationLabel;separator=", ") as ?occlabel) ?dateofbirth ?dateofdeath  (URI(CONCAT("https://www.animenewsnetwork.com/encyclopedia/people.php?id=",?ANNid)) AS ?ANN)
WHERE {
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P21 wd:Q6581072 .
  ?item wdt:P1982 ?ANNid .
  { ?item wdt:P106 wd:Q3455803 } UNION { ?item wdt:P106 wd:Q2059704 } UNION { ?item wdt:P106 wd:Q11284414 } UNION { ?item wdt:P106 wd:Q2526255 }
  ?item wdt:P106 ?occupation
  OPTIONAL { ?item wdt:P569 ?dateofbirth . }
  OPTIONAL { ?item wdt:P570 ?dateofdeath . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,ja" .
                         ?occupation rdfs:label ?occupationLabel .
                         ?item rdfs:label ?itemLabel .}
} group by ?item ?itemLabel ?dateofbirth ?dateofdeath ?ANNid
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 19:35, 18 August 2019 (UTC)
::{{ping|Tagishsimon}} I tried something similar. If I well remember, my only mistake was "group by" only by item and itemLabel instead of adding everything else... I don't think I can understand ''why'' it works, but I hope I can do this again in the future. Thanks a lot --[[User:Lombres|Lombres]] ([[User talk:Lombres|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:02, 18 August 2019 (UTC)

== Layers by property ==

I am still struggling with layers. All I want is one color for coordinates from ?pob and second color for coordinates from ?pod.
{{SPARQL|query=#defaultView:Map
SELECT ?sign ?signLabel ?pobLabel ?podLabel ?coorb ?coord ?layer WHERE {
  VALUES ?item { wd:Q66486391 }
  ?item wdt:P1891 ?sign.
  ?sign wdt:P19 ?pob .
  ?sign wdt:P20 ?pod .
  {?pob wdt:P625 ?coord .} union {?pod wdt:P625 ?coord .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}} --[[User:Jklamo|Jklamo]] ([[User talk:Jklamo|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 23:03, 18 August 2019 (UTC)

:{{ping|Jklamo}} you probably need 3 colours; the coord used may match both the pob & pod coord, or just pob, or just pod.
:{{SPARQL|query=#defaultView:Map
SELECT ?sign ?signLabel ?pobLabel ?podLabel ?coorb ?coord ?layer WHERE {
  VALUES ?item { wd:Q66486391 }
  ?item wdt:P1891 ?sign.
  ?sign wdt:P19 ?pob .
  ?sign wdt:P20 ?pod .
  {?pob wdt:P625 ?coord .} union {?pod wdt:P625 ?coord .}
  optional {?pob wdt:P625 ?pobcoord .} 
  optional {?pod wdt:P625 ?podcoord .}  
  bind(if(?coord = ?pobcoord && ?coord = ?podcoord, "pob & pod",if(?coord = ?pobcoord,"pob","pod")) as ?layer)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 23:36, 18 August 2019 (UTC)
::{{ping|Jklamo}} You are right, thanks for the query.--[[User:Jklamo|Jklamo]] ([[User talk:Jklamo|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 07:29, 19 August 2019 (UTC)

== Find a specific CAS number I'm searching for ==

How do I modify this so that it will search for a specific {{Q|P231}} number? I know that this number is present in the wikidata item {{Q|Q60863109}} but I want to learn a this query so that I can query any number I want. Thanks in advance!

 ?item wdt:P231 wd:8015-92-7.

[[User:Ελλίντερεστ|Ελλίντερεστ]] ([[User talk:Ελλίντερεστ|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 06:16, 19 August 2019 (UTC)
: {{ping|Ελλίντερεστ}} {{sparql|query=select ?item { ?item wdt:P231 "8015-92-7" . } }} <span style="border: 1px #C9C9C9;"><span style="background-color:#E8E8E8;padding:3px;">author&nbsp;</span><span style="background-color:#FFFFFF;">&nbsp;[[User:TomT0m|TomT0m]] / </span>talk [[User talk:TomT0m|page]]</span> 09:12, 19 August 2019 (UTC)

== Layer does not display ==

I have the following query:
{{SPARQL2|query=
#defaultView:Map
SELECT DISTINCT ?twin ?twinLabel ?geo ?pop ?image ?twinyear ?layer WHERE {
  ?city wdt:P190 ?twin .
  MINUS {?city wdt:P17 wd:Q30}
  ?twin wdt:P17 ?country .
  ?country wdt:P37 wd:Q1321 .
  ?twin wdt:P625 ?geo .
  ?twin wdt:P1082 ?pop .
  ?twin wdt:P571 ?inception .
  BIND(STR(YEAR(?inception)) AS ?twinyear)
  BIND(IF(?twinyear < 1700, "Pre-1700", 
          IF(?twinyear < 1751, "1700-1750", 
             IF(?twinyear < 1801, "1751-1800", 
                IF(?twinyear < 1851, "1801-1850", 
                   IF(?twinyear < 1901, "1851-1900", 
                      IF(?twinyear < 1951, "1901-1950", "Post-1950") 
                     ) 
                  ) 
               ) 
            )
         ) AS ?layer )
  FILTER (?pop < 100000 )
  FILTER (?pop > 10000 )
  OPTIONAL {?twin wdt:P18 ?image}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}}
I do not understand why the ?layer does not display. Any pointers? --[[User:Daniel Mietchen|Daniel Mietchen]] ([[User talk:Daniel Mietchen|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 07:54, 19 August 2019 (UTC)
:OK, now I see &mdash; removing the STR() from the ?twinyear line solves the problem:
::{{SPARQL2|query=
#defaultView:Map
SELECT DISTINCT ?twin ?twinLabel ?geo ?pop ?image ?twinyear ?layer WHERE {
  ?city wdt:P190 ?twin .
  MINUS {?city wdt:P17 wd:Q30}
  ?twin wdt:P17 ?country .
  ?country wdt:P37 wd:Q1321 .
  ?twin wdt:P625 ?geo .
  ?twin wdt:P1082 ?pop .
  ?twin wdt:P571 ?inception .
  BIND(YEAR(?inception) AS ?twinyear)
  BIND(IF(?twinyear < 1700, "Pre-1700", 
          IF(?twinyear < 1751, "1700-1750", 
             IF(?twinyear < 1801, "1751-1800", 
                IF(?twinyear < 1851, "1801-1850", 
                   IF(?twinyear < 1901, "1851-1900", 
                      IF(?twinyear < 1951, "1901-1950", "Post-1950") 
                     ) 
                  ) 
               ) 
            )
         ) AS ?layer )
  FILTER (?pop < 100000 )
  FILTER (?pop > 10000 )
  OPTIONAL {?twin wdt:P18 ?image}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}}
::--[[User:Daniel Mietchen|Daniel Mietchen]] ([[User talk:Daniel Mietchen|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 08:03, 19 August 2019 (UTC)
== Authors most cited with no entry in Wikidata ==

I want to have a list of all authors with no entry on Wikidata using {{P|2083}}, ordered by the most strings with the most occurences. The goal is to identify the most cited author with no entry in Wikidata, so we can create entries that would fit in the most articles already in WD.

From the [[Property talk:P2093|discussion page of the property article]] I have the query for an exact match:
{{SPARQL|query=
#Author name strings 
SELECT ?item ?itemLabel
{
  ?item wdt:P2093 "Jane Doe" .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
}}

This is not what I am looking for. If I try to have all the matchs displayed in a list, I have a time out error:

{{SPARQL|query=
#Author name strings 
SELECT ?item ?itemLabel ?value 
{
  ?item wdt:P2093 ?value .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
}}

Does anyone knows how I could have a successful request to identify which are those popular yet not in Wikidata authors?

Thanks, [[User:Dirac|Dirac]] ([[User talk:Dirac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 19:58, 7 August 2019 (UTC)

: {{ping|Dirac}} there are more than 94,000.000 statements using {{P|2093}} so imho it is unfortunately not possible to run a succesful sparql-query. to get an overview about the usage of {{P|2093}} you can use instead the following [https://query.wikidata.org/bigdata/ldf?subject=&predicate=http%3A%2F%2Fwww.wikidata.org%2Fprop%2Fstatement%2FP2093&object=&page=4 ldf-query] [[User:Mfchris84|Mfchris84]] ([[User talk:Mfchris84|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 11:39, 8 August 2019 (UTC)

::{{ping|Mfchris84}} Thanks for the answer. I didn't know the ldf-query tool. It seems handy for this type of request. However, one web page for every 100 results is not easy to manipulate. Is there a way to get the result in a text file?  I tried to add <code>&application/ld+json</code> at the end of the URL, with no luck. Should I use the [https://cran.r-project.org/web/packages/WikidataQueryServiceR/index.html WikidataQueryServiceR R package] or is there a more convenient (ie on-line) way of getting the results in one text file? Thanks, [[User:Dirac|Dirac]] ([[User talk:Dirac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:13, 8 August 2019 (UTC)

:::{{ping|Dirac}} The page size of LDF seems to be configurated server-side only. [https://www.mediawiki.org/wiki/Wikidata_Query_Service/User_Manual#Linked_Data_Fragments_endpoint LDF endpoint] As you have already written you can parse the result directly as json-ld but you have to write your own parser to iterate through the whole result set. Unfortunately i am not familiar with your mentioned R package. [[User:Mfchris84|Mfchris84]] ([[User talk:Mfchris84|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:39, 8 August 2019 (UTC)

::::{{ping|Mfchris84}}, I did parse the first 10k pages and, for your curiosity, here are the 10 most cited authors with no entry, starting with the number of occurences:
::::*  10263 Wei Wang
::::*   6136 Lei Zhang
::::*   6104 Li Zhang
::::*   6100 Li Li
::::*   6085 Yan Wang
::::*   4769 Hui Wang
::::*   4630 Jian Wang
::::*   4583 Hui Li
::::*   4470 Jie Zhang
::::*   4143 Jian Zhang
:::: Curiously, they all seems chinese. Thanks for the chat, that was fun to do! [[User:Dirac|Dirac]] ([[User talk:Dirac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:44, 8 August 2019 (UTC)
:::::It's the most frequent author name strings, not authors. I vaguely recall a paper attempting to determine the most prolific authors .. I think it came up with a Japanese university dean who had his name on most papers written there. --- [[User talk:Jura1|Jura]] 16:57, 8 August 2019 (UTC)
:::::{{ping|Dirac}} Funny stats - but as {{ping|Jura1}} already mentioned that are not the most cited distinct authors, that are only the most common author names. so there are many Wei Wangs out there to write so many papers. what plans do you have with the list of all author names? imho to create new author names or link to existing author items you need additional information or references to disambiguate them. [[User:Mfchris84|Mfchris84]] ([[User talk:Mfchris84|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 04:55, 9 August 2019 (UTC)
::::::{{ping|Mfchris84}} Indeed, there is [https://www.profweiwang.com/ Wei Wang], [http://iqua.ece.toronto.edu/~weiwang/ Wei Wang], [http://web.cs.ucla.edu/~weiwang/ Wei Wang] and he [http://www.wenweidance.ca/ even dances]!  I was a bit naive by creating this list, not expecting that so many people could have the same name. I may look at least common names, ones that sounds like people from Québec and see if I can identify their uniqueness and then create their element. Or not. Do you see another useful usage? [[User:Dirac|Dirac]] ([[User talk:Dirac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:37, 9 August 2019 (UTC)
::::::::You could pick one or several names and then disambiguation everybody else with the same name, e.g. [[Special:Search/Roger Moore haswbstatement:P31=Q5]]. --- [[User talk:Jura1|Jura]] 15:03, 9 August 2019 (UTC)
::::::::: <s>I realized that since there was 100 people per page, the 10K pages downloaded include the whole dataset. Hence, the classification includes everyone. </s> 100k * 100 = 1M, which is a little bit more than 1% of the whole dataset. The file for this 1% can be found online here: https://octet.ca/arc/most-popular-names_P2093_wikidata.txt
::::::::: [[User:Dirac|Dirac]] ([[User talk:Dirac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:50, 12 August 2019 (UTC)
:::::::::*It's more like 940k of pages: [https://query.wikidata.org/#SELECT%20%28%3Fcount%2F100%20as%20%3Fpages%29%20%7B%20SELECT%20%28COUNT%28%2a%29%20as%20%3Fcount%29%20%7B%20%3Fitem%20wdt%3AP2093%20%5B%5D%20%7D%20%7D]. --- [[User talk:Jura1|Jura]] 17:23, 12 August 2019 (UTC)
::::::::[[Q66426695]], found thanks to [[Wikidata:WikiProject Random|WikiProject Random]], probably appears in some 1000 papers on Wikidata .. but I think physicists are generally ignored in this type of stat .. --- [[User talk:Jura1|Jura]] 20:06, 13 August 2019 (UTC)
::::::::: {{ping|Mfchris84|Jura1}}, I've written [https://framagit.org/wikidata-tools/P2093-to-P50 an R script to address this]. It moves author from {{P|2093}} to {{P|50}} with corresponding qualifiers and references. I'll try it on [[Q66426695]]. Do you have an idea how I could find ''unique'' author names with a lot of entries? [[User:Dirac|Dirac]] ([[User talk:Dirac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:19, 17 August 2019 (UTC)
::::::::::{{ping|Dirac}} {{ping|Mfchris84}} I think it would be helpful to create and periodically update a list of values from P2093 (similar to the one you did) and check these. The problem with these values is that many are limited to an initial and a surname. If you delete common surnames, it might give better candidates, but it still doesn't exclude the possibility that you have people with the same given name(s) and family name writing in the similar field. BTW, have a look at https://tools.wmflabs.org/author-disambiguator/ sample: [https://tools.wmflabs.org/author-disambiguator/?name=Ana+Mar%C3%ADa+Calder%C3%B3n+de+la+Barca&fuzzy=1&doit=Look+for+author&limit=500&filter=]. --- [[User talk:Jura1|Jura]] 14:50, 20 August 2019 (UTC)

== HDS and WP ==

The editor of the Historical Dictionary of Switzerland (Q642074) is interested to have a list of all Wikipedia articles (for example in English) which have the HDS identifier P902. I hope this an easy query for an experienced Wikidatian. I tried to create such a query but wasn't able to do it. Thank you for your answer. --[[User:Hadi|Hadi]] ([[User talk:Hadi|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:57, 21 August 2019 (UTC)

:{{ping|Hadi}} Two queries for you. Item labels are fetched as your default language, and if a label in such a language does not exist, then the en, de or fr label in that order. You can amend the label choice in the wikibase:label statement.
:1. Wikidata items with HDS ID:
:{{SPARQL|query=SELECT ?item ?itemLabel ?HDS_ID
WHERE 
{
  ?item wdt:P902 ?HDS_ID.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr". }
} order by ?itemLabel
}}

:2. English Wikiperdia articles linked to from Wikidata items that have HDS ID. You can probably see that to adapt this to e.g. French wikipedia requires only amendment of the URL in schema:isPartOf <https://en.wikipedia.org/> .
:{{SPARQL|query=SELECT ?item ?itemLabel ?HDS_ID ?sitelink with { SELECT ?item ?sitelink ?HDS_ID
WHERE 
{
  ?item wdt:P902 ?HDS_ID.
  ?sitelink ^schema:name ?article .
  ?article schema:about ?item ;
          schema:isPartOf <https://en.wikipedia.org/> .
} } as %i
where
{
  include %i
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,fr". }
} order by ?itemLabel
}}

:It's possible to do more than this, so if you want amendments, please come back for more. hth --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 14:50, 21 August 2019 (UTC)
::Thank you very much [[User:Tagishsimon|Tagishsimon]]! As the HDS is published in 3 languages it's interesting for them to have also the Wikipedia languages separately. I will try these searches and come back if necessary. --[[User:Hadi|Hadi]] ([[User talk:Hadi|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 15:31, 21 August 2019 (UTC)
== Get cities and town ==

Can you help me to get cities and town certain country?
thanks a lot <small>&nbsp;&ndash; {{Autotranslate|base=Unsigned/text|1=77.222.144.250|2=14:53, 21 August 2019‎}}</small><!--Inserted with Template:Unsigned-->
: It's as easy as
{{SPARQL|query=
SELECT ?item ?itemLabel WHERE {
  ?item wdt:P31/wdt:P279* wd:Q7930989.
  ?item wdt:P17 wd:Q159.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}}
--[[User:Infovarius|Infovarius]] ([[User talk:Infovarius|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:19, 22 August 2019 (UTC)
: Иut in some countries there are also "communes" or "municipalities"... Are they count? --[[User:Infovarius|Infovarius]] ([[User talk:Infovarius|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:24, 22 August 2019 (UTC)

== get english wikipedia URL (page) for wikidata Q item ==

I have a query that needs to be finished. It gets 3 items but the 4th - I don't know how to do.
Also, how to make the NCT item optional (e.g., if no NCT property is there, don't require it)
Similarly the wikipedia article may not exist. So I want an outer join. (get all trials and some trials will not have wikipedia page and will not have nctid.
{{SPARQL5|query=
SELECT ?item ?itemLabel ?nctid ?enwikipediaArticleTitle
WHERE 
{
  ?item wdt:P31 wd:Q30612.
  ?item wdt:P3098 ?nctid
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}}
[[User:EncycloABC|EncycloABC]] ([[User talk:EncycloABC|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:48, 21 August 2019 (UTC)

:{{ping|EncycloABC}} None of them seem to have en.wiki articles; the code in the query would return article names were there any.
:{{SPARQL|query=SELECT ?item ?itemLabel ?nctid ?sitelink
WHERE 
{
  ?item wdt:P31 wd:Q30612.
  OPTIONAL { ?item wdt:P3098 ?nctid . }
  OPTIONAL { ?sitelink ^schema:name ?article .
             ?article schema:about ?item ;
                      schema:isPartOf <https://en.wikipedia.org/> . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:35, 21 August 2019 (UTC)
== Old taxonomists, missing from Wikisource ==

Please:

People who died before 1949, with a page in Wikispecies, but no page in any Wikisource. <span class="vcard"><span class="fn">[[User:Pigsonthewing|Andy Mabbett]]</span> (<span class="nickname">Pigsonthewing</span>); [[User talk:Pigsonthewing|Talk to Andy]]; [[Special:Contributions/Pigsonthewing|Andy's edits]]</span> 15:45, 21 August 2019 (UTC)
:{{SPARQL|query=SELECT ?item ?itemLabel ?species {
  ?item wdt:P31 wd:Q5 ; wdt:P570 ?dod. FILTER(YEAR(?dod) < 1949) # people who died before 1949
  ?species schema:about ?item; schema:isPartOf <https://species.wikimedia.org/> . # with a page in Wikispecies
  MINUS { ?source schema:about ?item . filter(contains(str(?source),"wikisource.org")) } # but no page in any Wikisource
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} }} [[User:Mahir256|Mahir256]] ([[User talk:Mahir256|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:12, 21 August 2019 (UTC)

:: Thank you. Someone on en.Wikisource adapted it, to remove some duplicates: https://w.wiki/7Mc -- <span class="vcard"><span class="fn">[[User:Pigsonthewing|Andy Mabbett]]</span> (<span class="nickname">Pigsonthewing</span>); [[User talk:Pigsonthewing|Talk to Andy]]; [[Special:Contributions/Pigsonthewing|Andy's edits]]</span> 10:53, 23 August 2019 (UTC)

== Hoping someone can modify this query to list for all administrative entities in Australia ==

[https://w.wiki/7Ns Query which lists number of artworks in wikidata for women whose work is held in institutions in the ACT]
{{SPARQL|query=#Australian paintings/drawing/print/sculpture by institution and female creator

SELECT ?creatorLabel ?institutionLabel (COUNT(*) AS ?count) WHERE {
  {?artwork wdt:P31 wd:Q3305213.}  # painting
  UNION
   {?artwork wdt:P31 wd:Q11060274.}# print
  UNION 
   {?artwork wdt:P31 wd:Q93184.}   # drawing
  UNION 
   {?artwork wdt:P31 wd:Q860861.}  # sculpture
  ?artwork wdt:P170 ?creator.      # define variable creator
  ?creator wdt:P21 wd:Q6581072.    # creator is female
  ?institution wdt:P131 wd:Q3258.  # institution in ACT
  ?artwork wdt:P195 ?institution.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?creatorLabel ?institutionLabel
ORDER BY ?count ?creatorLabel
}}
[[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:42, 23 August 2019 (UTC)

:{{ping|MargaretRDonald}} This - https://w.wiki/7PC - probably. --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:50, 23 August 2019 (UTC)
::Thanks very much, {{ping|Tagishsimon}} [[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 14:04, 23 August 2019 (UTC)
== Wikidata list ==

Hello. [[User:Xaris333/Ministers/Ministers of Defense]]. I want the Wikidata list to show the qualifiers P580, P581, P1545, P1365, P1366. I am sure if I have to change something in the query or to add the qualifiers (and how) to "columns". Maybe I have to ask to [[Template talk:Wikidata list]]?
: {{ping|Xaris333}} [https://www.wikidata.org/w/index.php?title=User:Xaris333/Ministers/Ministers_of_Defense&diff=998753739&oldid=998730195 {{done}}] <span style="border: 1px #C9C9C9;"><span style="background-color:#E8E8E8;padding:3px;">author&nbsp;</span><span style="background-color:#FFFFFF;">&nbsp;[[User:TomT0m|TomT0m]] / </span>talk [[User talk:TomT0m|page]]</span> 11:59, 19 August 2019 (UTC)
:: Many thanks!! [[User:Xaris333|Xaris333]] ([[User talk:Xaris333|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:58, 19 August 2019 (UTC)

{{ping|TomT0m}} can you change it to include also the items with {{P|39}} -> {{Q|66581831}}? [[User:Xaris333|Xaris333]] ([[User talk:Xaris333|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 14:00, 19 August 2019 (UTC)
:  {{ping|Xaris333}} {{done}} quickly, for some reason however the list showed by wdqs has less lines than the rew results of Wdqs. Some lines on wdqs are duplicated because of the duplicate predecessor/successors of some ministers. This seems to vanish post-treated by the bot, it seems there is only one result per minister. If you want refinements like this sorted out or more, like sectioning by function, it’s possible. <span style="border: 1px #C9C9C9;"><span style="background-color:#E8E8E8;padding:3px;">author&nbsp;</span><span style="background-color:#FFFFFF;">&nbsp;[[User:TomT0m|TomT0m]] / </span>talk [[User talk:TomT0m|page]]</span> 15:43, 19 August 2019 (UTC)

::{{ping|TomT0m}} I want to be able to have a full list. So if someone was minister twice, I want both periods, 2 lines. I want to have all the numbers in ?order column for example. [[User:Xaris333|Xaris333]] ([[User talk:Xaris333|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 16:07, 19 August 2019 (UTC)

:::Without getting too much involved, and iirc, Listeria will emit only one row per ?item in its query. So if an individual has held two positions and ?item is the individual, you get just one row / one position. So {{Q|65807458}} is one example - WDQS gives two rows, Listeria only one. Any work-around is sufficiently not trivial that I'm not going to go further; clearly this is territory in which one person may have held multiple positions; may have held the same positions multiple times; and in which positions may have been held by multiple persons - thus there's no quick & easy candidate for the ?item in the query. Group_concat (e.g. of the positions per individual) is possible, but that doesn't produce a simple tabular result. --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 15:44, 20 August 2019 (UTC)


{{SPARQL2|query=
SELECT ?office ?item ?itemLabel ?itemDescription ?start ?end ?order ?predecessor ?successor
{
  ?item p:P39 ?fc_stmt .
  ?fc_stmt ps:P39 ?office ; pq:P580 ?start  .
  values ?office { wd:Q65243720 wd:Q66581831 }
  optional { ?fc_stmt pq:P582 ?end } 
  optional { ?fc_stmt pq:P1545 ?order }
  optional { ?fc_stmt  pq:P1365 ?predecessor }
  optional { ?fc_stmt  pq:P1366 ?successor}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "el" }
}
}}

{{ping|Tagishsimon}}
Well I can use the above query. But there are two problems: The order is problematic, is not 1,2,3,4... but 1,10,11...,2,21... I want the order to be normal so I would be able to check the items. And the columns for office, item, predecessor and successor are the items numbers. I want to see the labels in Greek. [[User:Xaris333|Xaris333]] ([[User talk:Xaris333|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 03:27, 21 August 2019 (UTC)

:{{ping|Xaris333}} An amended query in WDQS seems straightforward: 
:{{SPARQL|query=SELECT ?officeLabel ?item ?itemLabel ?itemDescription ?start ?end ?order ?predecessorLabel ?successorLabel ?ordernumber
{
  ?item p:P39 ?fc_stmt .
  ?fc_stmt ps:P39 ?office ; pq:P580 ?start  .
  values ?office { wd:Q65243720 wd:Q66581831 }
  optional { ?fc_stmt pq:P582 ?end } 
  optional { ?fc_stmt pq:P1545 ?order 
    bind(if(strlen(str(?order))=1,concat("0",?order),?order) as  ?ordernumber)            
           }
  optional { ?fc_stmt  pq:P1365 ?predecessor }
  optional { ?fc_stmt  pq:P1366 ?successor}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "el" }
} order by ?ordernumber
}}

:But Listeria fetches the Greek item label without need for the label service; so the query in [https://www.wikidata.org/w/index.php?title=User:Tagishsimon/junk&oldid=1001136106 this example Listeria] /seems/ to do what you want. EXCEPT ... if you compare it with the WDQS report, you'll see that individuals like {{Q|7226186}} have two rows in WDQS, one in Listeria; cause is that the position statement in Q7226186 has two {{P|1365}} qualifiers. So either you need to do some group_concat stuff here, or else sort out the data (?) --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:17, 23 August 2019 (UTC)

Thanks Tagishsimon!! I can use both listeria and query for checking. ({{Q|7226186}} is a unique situation). [[User:Xaris333|Xaris333]] ([[User talk:Xaris333|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 04:24, 24 August 2019 (UTC)

== age not calculating/appearing ==

{{SPARQL|query=SELECT DISTINCT ?female ?femaleLabel ?dob ?birthplaceLabel ?dod ?age ?deathplaceLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?female wdt:P21 wd:Q6581072;             # is female
          wdt:P6379 wd:Q1464509.           # has works in the collection of the NGV
  bind(year(?dod)-year(?dob) as ?age )     # crude calculation of age
  OPTIONAL { ?female wdt:P569 ?dob. }
  OPTIONAL { ?female wdt:P570 ?dod. }
  OPTIONAL { ?female wdt:P19 ?birthplace. }
  OPTIONAL { ?female wdt:P20 ?deathplace. }
}
}}
[[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:23, 23 August 2019 (UTC)

:{{ping|MargaretRDonald}} Order (sometimes) matters:
:{{SPARQL|query=SELECT DISTINCT ?female ?femaleLabel ?dob ?birthplaceLabel ?dod ?age ?deathplaceLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?female wdt:P21 wd:Q6581072;             # is female
          wdt:P6379 wd:Q1464509.           # has works in the collection of the NGV
  OPTIONAL { ?female wdt:P569 ?dob. }
  OPTIONAL { ?female wdt:P570 ?dod. }
  OPTIONAL { ?female wdt:P19 ?birthplace. }
  OPTIONAL { ?female wdt:P20 ?deathplace. }
  bind(year(?dod)-year(?dob) as ?age )     # crude calculation of age
}
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:26, 23 August 2019 (UTC)
: Thanks again. {{ping|Tagishsimon}} [[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:29, 23 August 2019 (UTC)
== List of failed space missions ==

Hello everyone, I don't know how to query the list of failed space missions using Wikidata. Can anyone help me? {{unsigned|Midleading}}

*Any suggestion what to look for? Space mission + number of deaths? --- [[User talk:Jura1|Jura]] 14:42, 25 August 2019 (UTC)
== Newbie problems with sparql ==

I want to learn some sparql. I hope somebody can make me understand my failures. These are my mysteries at the moment:

===Simple intersect===
The following is supposed to match items that are an instance of both software and free and open-source software:
{{SPARQL|query=
SELECT ?item WHERE {
  ?item wdt:P31 wd:Q7397.
  ?item wdt:P31 wd:Q506883.
}
}}
It turns up nothing – which would be fine if there wasn't e.g. [[Q131344]] obviously matching the criteria.

:So that's as a result of the rank of the statements. Statements on items can have one of three ranks - preferred, normal, or deprecated. A query using wdt: asks for truthy statements, and these are defined as "the best non-deprecated rank for given property. Namely, if there is a preferred statement for property P2, then only preferred statements for P2 will be considered truthy. Otherwise, all normal-rank statements for P2 are considered truthy.". For {{Q|131344}}, {{Q|210337}} has a preferred rank, and is the only property that will be returned by a wdt: query. To query against all properties, use p:/ps:, so:

:{{SPARQL|query=
SELECT ?item WHERE {
  ?item p:P31/ps:P31 wd:Q7397.
  ?item p:P31/ps:P31 wd:Q506883.
}
}}

:There's some more info here - https://www.mediawiki.org/wiki/Wikibase/Indexing/RDF_Dump_Format#Statement_types --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:50, 26 August 2019 (UTC)

===More complex matching===
I tried to match items that have a license that is an instance of a free software license. I thought my solution worked until i tried to exclude stuff using <code>MINUS</code> and lots of items showed up that match the exclusion criterium like e.g. [[Q136775]] while using <code>MINUS</code> in simpler queries works as expected.
{{SPARQL|query=
SELECT ?item WHERE {
  ?item wdt:P275 ?license.
  ?license wdt:P31 ?subclass.
  ?subclass wdt:P279 wd:Q1156659.
  MINUS { ?item wdt:P31 wd:Q341. }
}
}}
--[[User:Reseletti|Reseletti]] ([[User talk:Reseletti|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:42, 26 August 2019 (UTC)

:{{ping|Reseletti}} Same issue as above. Try:
:{{SPARQL|query=SELECT ?item WHERE {
  ?item wdt:P275 ?license.
  ?license wdt:P31/wdt:P279* wd:Q1156659.
  MINUS { ?item p:P31/ps:P31 wd:Q341. }
}
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:54, 26 August 2019 (UTC)

::Also, see https://www.w3.org/TR/sparql11-property-paths/#path-language for info on constructions like wdt:P31/wdt:P279* --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:56, 26 August 2019 (UTC)
Thanks a lot!--[[User:Reseletti|Reseletti]] ([[User talk:Reseletti|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:31, 26 August 2019 (UTC)

== Generating labels with pairs of square brackets ==

{{SPARQL|query=SELECT ?item ?itemLabel?taxon_name ?NT_Flora_ID  WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
    ?item wdt:P5953 ?NT_Flora_ID.
    ?item wdt:P225 ?taxon_name.
    ?item wdt:P171 wd:Q81666.   # whose genus is Acacia
  MINUS
  {?article schema:about ?item. # and have an article about them
  ?article schema:isPartOf <https://en.wikipedia.org/> . # on *English* Wikipedia}
  }
}
ORDER BY ?itemLabel ?item ?taxon_name 
}}
I used the query above to generate a list of missing NT Acacia articles within Petscan and inserted the resulting wiki markup in my sandbox [[:en:User:MargaretRDonald/Sandbox/Use of PetScan to generate list of NT acacia articles to be written|to generate a list of articles to be written]].  However, when I click upon a label it takes me to a wikidata item. I want to generate a red wikilink which will turn blue when an article is completed, i.e., I want my labels to be, for example,  <nowiki>[[Acacia caracteae]]</nowiki>. [[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 02:49, 27 August 2019 (UTC)

:{{ping|MargaretRDonald}} This sounds like a Listeria redlist, such as [https://en.wikipedia.org/w/index.php?title=User:Tagishsimon/junk3&oldid=912683354 this example]. It'll refresh daily, removing blue-links. If you'd like it, please steal the page. --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 06:13, 27 August 2019 (UTC)

::But to answer the question; like this:
::{{SPARQL|query=SELECT ?item ?itemLabel ?taxon_name ?NT_Flora_ID  ?link WHERE {
  ?item rdfs:label ?itemLabel . filter(lang(?itemLabel)="en")                      
  ?item wdt:P5953 ?NT_Flora_ID.
  ?item wdt:P225 ?taxon_name.
  ?item wdt:P171 wd:Q81666.   # whose genus is Acacia
  bind(concat("[[",?itemLabel,"]]") as ?link)
  MINUS
  {?article schema:about ?item. # and have an article about them
  ?article schema:isPartOf <https://en.wikipedia.org/> . # on *English* Wikipedia}
  }
}
ORDER BY ?itemLabel ?item ?taxon_name
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 06:30, 27 August 2019 (UTC)
:Thanks, once more, {{ping|Tagishsimon}}.  [[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 08:22, 27 August 2019 (UTC)

== Border posts of EU ==

Hello there
Is it possible to show a map of all European (EU) border posts connected to non-EU (+UK) countries (for instance Lituania >> Russia but not Lituania >> Latvia)?[[User:Bouzinac|Bouzinac]] ([[User talk:Bouzinac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 14:25, 27 August 2019 (UTC)
== Fabrics by weave and fiber ==

How can I change [https://w.wiki/3yA this query] so I get one line per item with all of the values, instead of a separate line for each item/value combination? Thanks! - [[User:PKM|PKM]] ([[User talk:PKM|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 19:26, 21 August 2019 (UTC)

:{{ping|PKM}} Aggregation. Changes in three areas: 1) the select 2) the group by 3) in the wikibase:label to make the labels available for grouing. 
:{{SPARQL|query=SELECT ?woven_fabric ?woven_fabricLabel (group_concat(distinct ?fabrication_method;separator=", ") as ?fm) (group_concat(distinct ?fabrication_methodLabel;separator=", ") as ?fmL) (group_concat(distinct ?material_used;separator=", ") as ?mu) (group_concat(distinct ?material_usedLabel;separator=", ") as ?mul) (group_concat(distinct ?described_by_source;separator=", ") as ?dbs) (group_concat(distinct ?described_by_sourceLabel;separator=", ") as ?dbsL) 
WHERE 
{
  ?woven_fabric wdt:P279 wd:Q5295538.
  OPTIONAL { ?woven_fabric wdt:P2079 ?fabrication_method. }
  OPTIONAL { ?woven_fabric wdt:P186 ?material_used. }
  OPTIONAL { ?woven_fabric wdt:P1343 ?described_by_source. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                           ?fabrication_method rdfs:label ?fabrication_methodLabel .
                           ?material_used rdfs:label ?material_usedLabel . 
                           ?described_by_source rdfs:label ?described_by_sourceLabel .}
} group by ?woven_fabric ?woven_fabricLabel
LIMIT 1000
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:30, 21 August 2019 (UTC)
::{{ping|Tagishsimon}} Thanks! “woven_fabricLabel” is returning blank values - can you fix that? Also, is there a way to have the other entities not display a full URL? - [[User:PKM|PKM]] ([[User talk:PKM|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:03, 21 August 2019 (UTC)

:::{{ping|PKM}} this
:::{{SPARQL|query=SELECT ?woven_fabric ?woven_fabricLabel (group_concat(distinct ?fm1;separator=", ") as ?fm) (group_concat(distinct ?fabrication_methodLabel;separator=", ") as ?fmL) (group_concat(distinct ?mu1;separator=", ") as ?mu) (group_concat(distinct ?material_usedLabel;separator=", ") as ?mul) (group_concat(distinct ?dbs1;separator=", ") as ?dbs) (group_concat(distinct ?described_by_sourceLabel;separator=", ") as ?dbsL) 
WHERE 
{
  ?woven_fabric wdt:P279 wd:Q5295538.
  OPTIONAL { ?woven_fabric wdt:P2079 ?fabrication_method. bind(replace(str(?fabrication_method),"http://www.wikidata.org/entity/","") as ?fm1) }
  OPTIONAL { ?woven_fabric wdt:P186 ?material_used. bind(replace(str(?material_used),"http://www.wikidata.org/entity/","") as ?mu1) }
  OPTIONAL { ?woven_fabric wdt:P1343 ?described_by_source. bind(replace(str(?described_by_source),"http://www.wikidata.org/entity/","") as ?dbs1) }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
                           ?fabrication_method rdfs:label ?fabrication_methodLabel .
                           ?material_used rdfs:label ?material_usedLabel . 
                           ?described_by_source rdfs:label ?described_by_sourceLabel .
                           ?woven_fabric rdfs:label ?woven_fabricLabel . }
} group by ?woven_fabric ?woven_fabricLabel
LIMIT 1000
}}
::{{ping|Tagishsimon}} Perfect!! Thank you. - [[User:PKM|PKM]] ([[User talk:PKM|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:51, 27 August 2019 (UTC)

== Get a qualifier ==

Hi. In this query, I'd like to get, in addition, the qualifier P585 (?pointInTime) of the P1082 with rank preferred. Thanks, [[User:Amadalvarez|Amadalvarez]] ([[User talk:Amadalvarez|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 11:01, 27 August 2019 (UTC)

{{SPARQL|query=SELECT DISTINCT ?item ?itemLabel ?poblacio ?superficie ?imatge_de_l_escut_d_armes ?pointInTime  WHERE {
  ?item wdt:P31 wd:Q33146843;
    wdt:P4335 ?p4335.
  FILTER(STRSTARTS(?p4335, "25"))
 
  OPTIONAL { ?item wdt:P1082 ?poblacio.}
  OPTIONAL { ?item wdt:P2046 ?superficie. }
  OPTIONAL { ?item wdt:P94 ?imatge_de_l_escut_d_armes. }
 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100

}}

:{{ping|Amadalvarez}}
:{{SPARQL|query=SELECT DISTINCT ?item ?itemLabel ?poblacio ?superficie ?imatge_de_l_escut_d_armes ?pointInTime WHERE {
  ?item wdt:P31 wd:Q33146843;
    wdt:P4335 ?p4335.
  FILTER(STRSTARTS(?p4335, "25"))
  OPTIONAL { ?item p:P1082 [ps:P1082 ?poblacio; pq:P585 ?pointInTime; wikibase:rank wikibase:PreferredRank] . }
  OPTIONAL { ?item wdt:P2046 ?superficie. }
  OPTIONAL { ?item wdt:P94 ?imatge_de_l_escut_d_armes. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
LIMIT 100
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:39, 27 August 2019 (UTC)

:Thanks a lot !!. [[User:Amadalvarez|Amadalvarez]] ([[User talk:Amadalvarez|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 04:11, 28 August 2019 (UTC)

== Malformed count ==

{{SPARQL|query=# Australian women artist with institution count

SELECT ?artist ?artistLabel (COUNT(?institution) as ?count) WHERE {

   {?artist wdt:P106 wd:Q483501.}     # any form of artist
  UNION
   {?artist wdt:P106 wd:Q11569986 .}  # occupation printmaker
  UNION 
   {?artist wdt:P106 wd:Q1028181.}    # occupation painter
  UNION 
   {?artist wdt:P106 wd:Q1281618.}    # occupation sculptor
 
   ?artist wdt:P31 wd:Q5.             # is human
   ?artist wdt:P21 wd:Q6581072.       # is female
   ?artist wdt:P6379 ?institution.    # has works in ?institution
   ?institution wdt:P131* wd:Q408.    # institution in Australia

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?artist
ORDER BY DESC (?count)
}}
(I thought I knew how to do this!!   But clearly I don't) [[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:43, 27 August 2019 (UTC)

:{{ping|MargaretRDonald}} I'm afraid this got complicated. Your query fails 'cos the GROUP BY lacks ?artistLabel - should be <code>GROUP BY ?artist ?artistLabel</code>, but even if we fix that, it looks as though the query will pick up an artist multiple times if they have multiple occupations matching the four you specify, and when grouping, produce a count which is is a multiple of the real count - {{Q|4858957}} gets picked up three times, ends up with a count of 12. 
:So the below uses subqueries; the first query gets all ?artist that qualify and the second query does the count. Part of the qualification is <code>?artist wdt:P6379/wdt:P131* wd:Q408. </code> - they have a work in an Oz inst ... this causes a timeout unless the magic <code>hint:Prior hint:gearing "forward".</code> is used. Some more on both of these in [[Wikidata:SPARQL query service/query optimization]]. 
:{{SPARQL|query=# Australian women artist with institution count

SELECT ?artist ?artistLabel (COUNT(?institution) as ?count) with { select distinct ?artist WHERE 
{
   {?artist wdt:P106 wd:Q483501.}     # any form of artist
  UNION
   {?artist wdt:P106 wd:Q11569986 .}  # occupation printmaker
  UNION 
   {?artist wdt:P106 wd:Q1028181.}    # occupation painter
  UNION 
   {?artist wdt:P106 wd:Q1281618.}    # occupation sculptor
   ?artist wdt:P31 wd:Q5.             # is human
   ?artist wdt:P21 wd:Q6581072.       # is female
   ?artist wdt:P6379/wdt:P131* wd:Q408.    # has work in institution in Australia
   hint:Prior hint:gearing "forward".
} } as %i where
{
  include %i
  ?artist wdt:P6379 ?institution.    # has works in ?institution
  ?institution wdt:P131* wd:Q408.    # institution in Australia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?artist ?artistLabel
ORDER BY DESC (?count)
}} 

:It's also possible that using VALUES might have been quicker / easier than the unions:

:{{SPARQL|query=# Australian women artist with institution count

SELECT ?artist ?artistLabel (COUNT(?institution) as ?count) with { select distinct ?artist WHERE 
{
   ?artist wdt:P106 ?occ.
   VALUES ?occ {wd:Q483501 wd:Q11569986 wd:Q1028181 wd:Q1281618}    # occupations
   ?artist wdt:P31 wd:Q5.             # is human
   ?artist wdt:P21 wd:Q6581072.       # is female
   ?artist wdt:P6379/wdt:P131* wd:Q408.    # has work in institution in Australia
   hint:Prior hint:gearing "forward".
} } as %i where
{
  include %i
  ?artist wdt:P6379 ?institution.    # has works in ?institution
  ?institution wdt:P131* wd:Q408.    # institution in Australia
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?artist ?artistLabel
ORDER BY DESC (?count)
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:08, 27 August 2019 (UTC)
::Thanks, yet again, {{ping|Tagishsimon}}.  Very helpful indeed... Some close scrutiny required at this end.  Cheers, [[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:34, 27 August 2019 (UTC)
== more than 1 value ==

Hello,
Please tell me a request that give: the items which are in the tree of subclasses of Q20162172 and have more than 1 P279 value. [[User:Visite fortuitement prolongée|Visite fortuitement prolongée]] ([[User talk:Visite fortuitement prolongée|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:10, 26 August 2019 (UTC)

:{{ping|Visite fortuitement prolongée}} something along these lines. ?items with more than 2 P279s get multiple rows - such as {{Q|5636912}}. 
:{{SPARQL|query=SELECT ?item ?itemLabel ?P279_1 ?P279_1Label ?P279_2 ?P279_2Label WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q20162172 .
  ?item wdt:P279 ?P279_1 .
  ?item wdt:P279 ?P279_2 .
  filter(str(?P279_1) < str(?P279_2) ) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:22, 26 August 2019 (UTC)
:: Thank you! [[User:Visite fortuitement prolongée|Visite fortuitement prolongée]] ([[User talk:Visite fortuitement prolongée|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 20:34, 26 August 2019 (UTC)

=== exactly 1 value ===
Theoretical problem: how to get items with ''exactly'' 1 value of specific property? --[[User:Infovarius|Infovarius]] ([[User talk:Infovarius|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 10:13, 28 August 2019 (UTC)

== Format constraint to exclude titles with two or more "$" ==

{{SPARQL5|query=SELECT ?item ?value ?test ?formatconstraint
{
  VALUES ?item { wd:Q18689133 #zero pass
                           wd:Q7711690 #one   pass 
                           wd:Q29302883 wd:Q29396284 #two fail
                           wd:Q24806537 #more fail
                           wd:wd:Q29301301 wd:Q27012930 #pair fail                       
                }          
  ?item wdt:P1476 ?value .
  BIND( !REGEX( STR( ?value ), "\\$[^$]*\\$" )  as ?test) .          
  BIND( REGEX( STR( ?value ), "^[^$]+$" )  as ?formatconstraint) .          
} 
ORDER BY ?test
}}

To find such titles, I'm tying to define a format constraint on {{P|1476}} that ''doesn't'' match titles with two or more "$" (?test=false in the above does that). 

To make it into a format constraint, this needs to be written differently (second regex), but somehow I don't quite get there. --- [[User talk:Jura1|Jura]] 11:59, 28 August 2019 (UTC)
:{{ping|Jura1}}: I think the RegEx <code>^[^$]*(\\$[^$]*)?$</code> will solve you problem. Here is the complete query.
:{{SPARQL|query=SELECT ?item ?value ?test ?formatconstraint
{
  VALUES ?item { wd:Q18689133 #zero pass
                           wd:Q7711690 #one   pass 
                           wd:Q29302883 wd:Q29396284 #two fail
                           wd:Q24806537 #more fail
                           wd:wd:Q29301301 wd:Q27012930 #pair fail                       
                }          
  ?item wdt:P1476 ?value .
  BIND( !REGEX( STR( ?value ), "\\$[^$]*\\$" )  as ?test) .          
  BIND( REGEX( STR( ?value ), "^[^$]*(\\$[^$]*)?$" )  as ?formatconstraint) .          
} 
ORDER BY ?test
}}
: I hope this helps. --[[User:Frog23|Frog23]] ([[User talk:Frog23|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:23, 28 August 2019 (UTC)
== Selecting the first of several equally ranked statements ==

I was working on a little query about movies from certain directors and wanted to sort them by year. Here is an example for {{Q|8877}}.
{{SPARQL2|query=#movies by Steven Spielberg sorted by year
SELECT DISTINCT ?movie ?movieLabel ?year WHERE {
  ?movie wdt:P31/wdt:P279* wd:Q11424.
  ?movie wdt:P57 wd:Q8877.
  ?movie wdt:P577 ?date.
  BIND (year(?date) as ?year)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY ?year
}}
However the problem is that some movies were released in different years in different countries. For instance {{Q|320588}} was released in 1977 in the US and in 1978 in Sweden and Germany. I know I could write the query to look where the qualifier {{P|291}} is {{Q|30}}, but I am interested in the general solution. How can I select only one out of several equally ranked statements, either by sorting them (like in this case of the date) or by just selecting the first (sometimes this is sufficient and I don't want to have to handle multiple answers to keep my result set concise)? Thanks for any hints on how to handle this. --[[User:Frog23|Frog23]] ([[User talk:Frog23|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:43, 28 August 2019 (UTC)

:{{ping|Frog23}} Aggregation and the use of MIN, MAX, SAMPLE, &c?
:{{SPARQL|query=#movies by Steven Spielberg sorted by year
SELECT DISTINCT ?movie ?movieLabel (min(?year) as ?myear) WHERE {
  ?movie wdt:P31/wdt:P279* wd:Q11424.
  ?movie wdt:P57 wd:Q8877.
  ?movie wdt:P577 ?date.
  BIND (year(?date) as ?year)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} group by ?movie ?movieLabel ORDER BY ?year
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 14:12, 28 August 2019 (UTC)

::{{ping|Tagishsimon}} Thanks, this works great. --[[User:Frog23|Frog23]] ([[User talk:Frog23|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 21:01, 28 August 2019 (UTC)

== Showing the year part only in a timeline ==

{{SPARQL|query=# AGSA artworks/ paintings/drawing/print/sculpture by year of inceptin
#defaultView:Timeline
SELECT DISTINCT ?date ?year  (COUNT(?artwork) AS ?count) WHERE {
  {?artwork wdt:P31 wd:Q3305213.}    # painting
  UNION
   {?artwork wdt:P31 wd:Q11060274.}  # print
  UNION 
   {?artwork wdt:P31 wd:Q93184.}     # drawing
  UNION 
   {?artwork wdt:P31 wd:Q860861.}    # sculpture
    UNION 
   {?artwork wdt:P31 wd:Q42314818.}  # visual art
   ?artwork wdt:P195 wd:Q705557.     # Art work is in AGSA
   ?artwork wdt:P571 ?date
  BIND(str(YEAR(?date)) AS ?year)           
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?date ?year
}}
The query counts the artworks at AGSA by year and shows a timeline.  Ideally, the only the YEAR part should display. [[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 03:04, 29 August 2019 (UTC)

:{{ping|MargaretRDonald}}
:Replace #defaultView:Timeline with #defaultView:Timeline{"hide": "?date"}
:--[[User:Larske|Larske]] ([[User talk:Larske|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 07:44, 29 August 2019 (UTC)
::Thanks, very much, {{ping|Larske}}  [[User:MargaretRDonald|MargaretRDonald]] ([[User talk:MargaretRDonald|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 18:15, 29 August 2019 (UTC)
== Public artworks in London by female artists ==

Could someone please show me how to do a query for public artworks in London where the {{P|170}} is female? A query for public artworks in London is already at [[:en:Wikipedia:WikiProject Public Art/London/Wikidata]]. [[User:Ham II|Ham II]] ([[User talk:Ham II|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:37, 30 August 2019 (UTC)

:{{ping|Ham II}} Extending the query you pointed to gives:
:{{SPARQL|query=SELECT ?item WHERE 
{ 
  ?item wdt:P136 wd:Q557141 . 
  ?item wdt:P131 ?sub0 . 
  ?sub0 (wdt:P131)* wd:Q23939248 .
  ?item wdt:P170/wdt:P21 wd:Q6581072 .
}
}}

:which is fine for Listeria. The last line says that the ?item must have a ?creator (P170) who has a P21 gender of female; it uses wdt:P170/wdt:P21 to chain these two concepts together - a property path.

:For WDQS, perhaps:
:{{SPARQL|query=SELECT ?item ?itemLabel ?creator ?creatorLabel WHERE 
{ 
  ?item wdt:P136 wd:Q557141 . 
  ?item wdt:P131 ?sub0 . 
  ?sub0 (wdt:P131)* wd:Q23939248 .
  ?item wdt:P170 ?creator .
  ?creator wdt:P21 wd:Q6581072 .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:42, 30 August 2019 (UTC)

:This one splits out the relation ?item must have ?creator, and ?creator must be female, so that we can put a ?creator column in the results; it also uses the label service to get labels, something not required for Listeria. hth --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 13:43, 30 August 2019 (UTC)

::{{ping|Tagishsimon}} Thank you so much! [[User:Ham II|Ham II]] ([[User talk:Ham II|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 15:19, 30 August 2019 (UTC)

== Request all (or most!) data for Netflix Original Titles ==

Hi all, completely new to SPARQL and have what I believe is a pretty simple query (or should be). I'm trying to get a list of all Netflix Originals (films AND television series). Ideally, I also want to get some addition info, where possible, for each of these titles, such as genre, country of production, number of episodes in total, etc.

I've spent the last couple of days reading through various help guides and also watched a really helpful tutorial on [https://www.youtube.com/watch?v=kJph4q0Im98&list=PL1YXJOcza_62lVLBpES15EuY8sdz9-kVE YouTube]. Having done this, I'm able to put together a pretty simple query, which seems to generate a list of Netflix Originals (films AND television series) with some additional info, where available, for each of these titles. Here's the code that I'm using:

{{SPARQL|query=SELECT ?titleLabel ?item ?itemLabel ?genreLabel ?locationLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q2431196. # instances of subclass audiovisual works - have done this because I want to find all films AND television series. Would UNION be another way to approach this?
  ?item wdt:P449 wd:Q907311. # original network is Netflix
  ?item wdt:P179 ?title .  # can't really explain how/why I've done this! But it seems to be the best way to find and display the title of a TV series
  OPTIONAL {?title wdt:P136 ?genre. }
  OPTIONAL {?title wdt:P840 ?location. } # these are just two optional values. I would like more in future, such as number of episodes, etc. 
                                         # But I'm keeping it simple for the moment
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } ORDER BY ?titleLabel
}}

This returns some 729 results. There aren't that many Netflix Originals, and the reason this number is so high is because it is returning a row for every episode of each Netflix Original television series. I only want to return one row for each series - I'm not really that interested in the level of detail of episode at the moment. I'm sure the solution to this is very simple, and that I'm probably going about things the wrong way with the code above, so I'd really appreciate any input on this. 

And one last question - another the reason the list is so long is because not only are there rows for every episode, there are also multiple rows for episodes with more than one genre. If and when I manage to get this to return just titles of programmes/films, I assume it's going to give me multiple rows for each instance where there are multiple genres. Is there a way to just choose the first instance of the genre (which presumably is the primary genre) and/or have all genres appear in the same row?

{{unsigned}}


Maybe something like the following can work for you. I skipped P31 entirely, but use MINUS to exclude episodes and seasons. 

{{SPARQL5|query=SELECT ?title ?item ?itemLabel ?itemDescription ?genreLabel ?narrlocationLabel
WHERE 
{
  ?item wdt:P449 wd:Q907311.
  OPTIONAL {?item wdt:P1476 ?title }  
  OPTIONAL {?item wdt:P136 ?genre. }
  OPTIONAL {?item wdt:P840 ?narrlocation. }
  MINUS { ?item wdt:P31 wd:Q21191270 }
  MINUS { ?item wdt:P31 wd:Q3464665 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} 
ORDER BY ?title
}}  --- [[User talk:Jura1|Jura]] 15:08, 30 August 2019 (UTC)


Below a version that groups by film/series:
{{SPARQL5|query=SELECT
  ?item ?itemLabel ?itemDescription 
  (SAMPLE(?title) as ?title) 
  (GROUP_CONCAT(DISTINCT ?genreLabel; separator= ", ") as ?genres) 
  (GROUP_CONCAT(DISTINCT ?locationLabel; separator= ", ") as ?narrlocs) 
WHERE
{
    { SELECT ?item ?itemLabel ?itemDescription ?title ?genreLabel ?locationLabel 
    WHERE 
    {
        hint:Query hint:optimizer "None".
        ?item wdt:P449 wd:Q907311.
        OPTIONAL {?item wdt:P1476 ?title }  
        OPTIONAL {?item wdt:P136 ?genre. }
        OPTIONAL {?item wdt:P840 ?location. } 
        MINUS { ?item wdt:P31 wd:Q21191270 }
        MINUS { ?item wdt:P31 wd:Q3464665 }
      SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
    }
   }
}          
GROUP BY ?item ?itemLabel ?itemDescription
ORDER BY ?title
}} --- [[User talk:Jura1|Jura]] 15:18, 30 August 2019 (UTC)

This works perfectly, thanks Jura! Not quite sure I understand ''how'' it works, but I can reverse engineer this, so to speak.
[[User:Japeskay|Japeskay]] ([[User talk:Japeskay|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]])

== Can someone tell me how to get results with distinct values in the "col1" column? ==

SELECT DISTINCT ?x ?y (?xLabel as ?col1) ("death" as ?col2) WHERE 
{ 
  ?x wdt:P1196 ?y . 
  ?y rdfs:label ?xLabel .
  filter (lang(?xLabel)="en")
  
}


I get results like:
 x               y              col1                    col2
 wd:Q65038299	 wd:Q198	war	                death
 wd:Q23661525	 wd:Q198	war	                death
 wd:Q55261312	 wd:Q198	war	                death
 wd:Q28599127	 wd:Q198	capital punishment	death
 wd:Q12023977	 wd:Q198	war	                death

I want results of the form:

 x               y              col1                    col2
 wd:Q28599127	 wd:Q198	capital punishment	death
 wd:Q12023977	 wd:Q198	war	                death

If someone can do better, then I would prefer this:

 col1                   col2
 capital punishment	death
 war	                death

That is, just give me results which are distinct on col1

:Not sure if I'm going in the right direction, but:
:{{SPARQL|query=SELECT DISTINCT (?xLabel as ?col1) ("death" as ?col2) WHERE {

 ?x wdt:P1196 ?y . 
 ?y rdfs:label ?xLabel .
 filter (lang(?xLabel)="en")
} group by ?xLabel 
}} --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 15:28, 30 August 2019 (UTC)
*Gee {{P|1196}} has gotten messy. --- [[User talk:Jura1|Jura]] 15:41, 30 August 2019 (UTC)

:Yes, some "creative" values are used for the {{P|P1196}} property, but the seven most common values account for 99.3 percent of all {{P|P1196}} claims whereas the other 243 values account for just 0.7 percent, see this query:
:{{SPARQL|query=SELECT DISTINCT ?p1196label (COUNT(?x) AS ?count) WHERE {
 ?x wdt:P1196 [rdfs:label ?p1196label] .
 filter (lang(?p1196label)="en")
} group by ?p1196label
ORDER BY DESC(?count) ?p1196label
}}
:--[[User:Larske|Larske]] ([[User talk:Larske|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 17:05, 30 August 2019 (UTC)
::<raises one eyebrow on seeing> <code> ?x wdt:P1196 [rdfs:label ?p1196label] . </code>--[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 17:38, 30 August 2019 (UTC)

Hey thanks, this helps a lot! Can you also answer the other queries on this page?
== Filter when a specific qualifier ==

Hello
Like this element, {{Q|Q26422032}} which I do not wish to see, how to filter elements having    both {{P|P361}}={{Q|Q1292279}}  and any qualifyer of  {{P|P576}} in {{P|P361}}  ?
something like this  <code lang=sparql>MINUS    { ?item wdt:P361 wd:Q1292279  pq:P576 _:b2.}</code> ? Thanks ! [[User:Bouzinac|Bouzinac]] ([[User talk:Bouzinac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 08:11, 31 August 2019 (UTC)
:{{ping|Bouzinac}} Yes, that style:  <code lang=sparql> MINUS { ?item p:P361 [ps:P361 wd:Q1292279 ; pq:P576 ?P576 ] . } </code> --[[User:Tagishsimon|Tagishsimon]] ([[User talk:Tagishsimon|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:46, 31 August 2019 (UTC)
:{{ping|Tagishsimon}} :-)[[User:Bouzinac|Bouzinac]] ([[User talk:Bouzinac|<span class="signature-talk">{{int:Talkpagelinktext}}</span>]]) 12:57, 31 August 2019 (UTC)