Fork of
Category path of article
by JAAqqO
This query is marked as a draft
This query has been published
by JAAqqO.
SQL
x
SET @basecat='Deaths_by_year';
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(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_title = 'Elena_Kagan_Supreme_Court_nomination';
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.