Replicas browser and optimizer
This query is marked as a draft
This query has been published
List creators and creation date for Wikimedia commons files in a given category. Last run November 13, 2014
#1 SELECT rev_user_text, rev_user, rev_id, rev_timestamp, page_title, cl_to #2 FROM commonswiki_p.page p #3 INNER JOIN commonswiki_p.categorylinks cl ON p.page_id = cl.cl_from #4 INNER JOIN commonswiki_p.revision r ON p.page_id = r.rev_page #5 WHERE p.page_namespace = 6 #6 AND cl.cl_to IN ("Views_of_Seattle_from_Elliott_Bay") #7 AND r.rev_parent_id = 0; #LINE 1: declare which database fields (columns) you want to include in your output. #You can include any fields from any of the tables you are joining (in this case: page, categorylinks, revision) #LINE 2: declare which wiki you are pulling data from. #In this case, commonswiki_p. You can find a full list here: https://tools.wmflabs.org/tools-info/?dblist=s1.labsdb #LINE 3: join the page table with the categorylinks table on their COMMON KEY, which is the id of the page. #LINE 4: join the page table with the revision table on their COMMON KEY, which is the id of the page. #LINE 5: specify the numeric namespace(s) of the pages you want to get information for. #There is a list of standard namespaces here: https://en.wikipedia.org/wiki/Wikipedia:Namespace #LINE 6: specify the category(ies) that the pages are in. If you wanted to get pages from multipe categories, #you would write the query the same way: AND cl.cl_to IN ("My_first_category", "Another_category") #LINE 7: specify that you want to return the revision information from the FIRST revision to the page. #the "parent" of a revision is the previous revision to that page. #The first revision to a page doesn't have a "parent", so the value for rev_parent_id will be 0.
By running queries you agree to the
and you irrevocably agree to release your SQL under
All SQL code is licensed under
Checking query status...
Report a bug!
Wikimedia Cloud VPS
, written in