Ring SQL query

From DISI
Revision as of 16:14, 10 March 2014 by Frodo (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, 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;