Most frequent dead external links in articles on plwiki

SQL

USE plwiki_p;
SELECT tmp.el_to, COUNT(*) AS counting
FROM (SELECT el_to, COUNT(*) AS counting
	FROM page
	JOIN externallinks
	ON page.page_id = externallinks.el_from
	WHERE tmp.el_to NOT LIKE 'http://web.archive.org/%'
	AND tmp.el_to NOT LIKE 'http://toolserver.org/%'
	AND page_namespace = 1
	GROUP BY el_to
	HAVING counting >= 12) tmp
JOIN externallinks
ON page.page_id = externallinks.el_from
WHERE page_namespace = 0
ORDER BY counting DESC, el_to;


/*SELECT el_to, COUNT(*) AS counting
FROM page
JOIN externallinks
ON page.page_id = externallinks.el_from
WHERE el_to NOT LIKE 'http://web.archive.org/%'
AND el_to NOT LIKE 'http://toolserver.org/%'
AND page_namespace = 1
GROUP BY el_to
HAVING counting >= 12
ORDER BY counting DESC, el_to;*/

SQL

Checking query status...