Ring SQL query: Difference between revisions

From DISI
Jump to navigation Jump to search
No edit summary
No edit summary
 
Line 27: Line 27:
</pre>
</pre>


[[Category:SQL]]
[[Category:Developer]]
[[Category:Internal]]
[[Category:Cheminformatics]]
[[Category:Cheminformatics]]

Latest revision as of 00:14, 11 March 2014

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;