Ring SQL query

From DISI
Revision as of 00:14, 11 March 2014 by Frodo (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

From his bestseller, 101 things to not forget

This just came out of some debugging. Thought you may be interested. It will return the number of occurrences of a particular ring 

SELECT * FROM (
	SELECT 
		short_desc, 
		COUNT(n.sub_id_fk) AS occurrences, 
		nc.size AS annotations, 
		(COUNT(n.sub_id_fk)/nc.size) AS ratio 
	FROM note AS n 
	INNER JOIN annotation AS a ON n.anno_id_fk=a.anno_id 
	INNER JOIN 
		(SELECT anno_id_fk, COUNT(sub_id_fk) AS size FROM note GROUP BY anno_id_fk) 
		AS nc ON a.anno_id=nc.anno_id_fk 
	INNER JOIN hasring AS hr ON hr.sub_id_fk=n.sub_id_fk 
	WHERE hr.rings_fk IN (1) --<YOU CAN CHANGE THIS TO ANY # OF RING IDS>
	GROUP BY a.anno_id 
	ORDER BY (COUNT(n.sub_id_fk)/annotations) DESC, annotations DESC
) 
AS anno_rings 
WHERE ratio > 0.001 
AND size > 1 
AND occurrences > 1;