Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Cryptic
.
Non-redirect mainspace pages containing a Greek letter name in Latin script (case-insensitive, distinct word) that are in a subcategory of [[Category:Chemistry]] to a maximum depth of 7. For [[WP:RAQ#Spelled out Latin characters in Chemistry articles]] circa 9 July 2023.
Toggle Highlighting
SQL
SET @basecat='Chemistry'; SET max_recursive_iterations=7; WITH RECURSIVE deepcat (subcat, catpath, depth) AS ( SELECT REPLACE(@basecat, '_', ' '), REPLACE(@basecat, '_', ' '), 0 UNION SELECT page_title, CONCAT(catpath, ' > ', REPLACE(page_title, '_', ' ')), depth + 1 FROM categorylinks JOIN page ON page_id = cl_from AND page_namespace = 14 JOIN deepcat ON cl_to = subcat ) SELECT DISTINCT REPLACE(page_title, '_', ' ') AS title, MIN(depth), MIN(catpath) OVER (PARTITION BY page_title ORDER BY depth ASC) AS 'category path' FROM page JOIN categorylinks ON cl_from = page_id JOIN deepcat ON cl_to = subcat WHERE page_namespace = 0 AND page_is_redirect = 0 AND LOWER(CONVERT(page_title USING 'utf8')) RLIKE '(\\b|_)(alpha|beta|gamma|delta|epsilon|zeta|eta|theta|iota|kappa|lambda|mu|nu|xi|omicron|pi|rho|sigma|tau|upsilon|phi|chi|psi|omega)(\\b|_)' GROUP BY page_title ORDER BY MIN(depth) ASC;
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...