Most linked disambiguations (lvwiki)

SQL

USE lvwiki_p;
SELECT p.page_namespace, p.page_title, COUNT(pl.pl_from) FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = 'Nozīmju_atdalīšana'
# INNER skips pages whose link count is zero. Change INNER to LEFT on the line below if you also want to include those:
INNER JOIN pagelinks pl ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
# This counts all links, including links from talk pages to disambiguation pages. If you only want to count
# links from the article namespace, then remove the # from the line below:
# AND pl.pl_from_namespace = 0
GROUP BY p.page_title
ORDER BY COUNT(pl.pl_from) DESC

/*
USE lvwiki_p;
SELECT count(page1.page_title), page2.page_title
FROM page AS page1
JOIN pagelinks ON page1.page_id = pagelinks.pl_from AND page1.page_namespace = 0
JOIN page AS page2 ON pagelinks.pl_namespace = page2.page_namespace AND pagelinks.pl_title = page2.page_title
JOIN categorylinks ON page2.page_id = categorylinks.cl_from
AND categorylinks.cl_to = 'nozīmju atdalīšana'
AND not exists
(select * from categorylinks as cl1
 where cl1.cl_from = page1.page_id
 and cl1.cl_to = 'nozīmju atdalīšana')
Group by page2.page_title
#ORDER BY COUNT(page2.page_title) DESC

USE lvwiki_p;
SELECT count(page1.page_title), page2.page_title
FROM page AS page1
JOIN pagelinks ON page1.page_id = pagelinks.pl_from AND page1.page_namespace = 0
JOIN page AS page2 ON pagelinks.pl_namespace = page2.page_namespace AND pagelinks.pl_title = page2.page_title
JOIN categorylinks ON page2.page_id = categorylinks.cl_from
AND categorylinks.cl_to = 'nozīmju atdalīšana'
AND not exists
(select * from categorylinks as cl1
 where cl1.cl_from = page1.page_id
 and cl1.cl_to = 'nozīmju atdalīšana')
Group by page2.page_title
#ORDER BY COUNT(page2.page_title) DESC

otrais - nozimju atdalisanas lapas

USE lvwiki_p;
SELECT page1.page_title, page2.page_title
FROM page AS page1
JOIN pagelinks ON page1.page_id = pagelinks.pl_from AND page1.page_namespace = 0
JOIN page AS page2 ON pagelinks.pl_namespace = page2.page_namespace AND pagelinks.pl_title = page2.page_title
JOIN categorylinks ON page2.page_id = categorylinks.cl_from
AND categorylinks.cl_to = 'nozīmju atdalīšana'
AND not exists
(select * from categorylinks as cl1
 where cl1.cl_from = page1.page_id
 and cl1.cl_to = 'nozīmju atdalīšana')


USE lvwiki_p;
SELECT p.page_title, COUNT(p2.pl_title) FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = "Nozīmju_atdalīšana"
LEFT JOIN pagelinks p2 ON p.page_id = p2.pl_from AND p2.pl_namespace = 0 #AND p2.pl_title IN ('Reflist', 'Denmark-writer-stub')
GROUP BY p.page_title
ORDER BY COUNT(p2.pl_title) DESC
LIMIT 30
*/
SQL

Checking query status...
