Ring SQL query: Difference between revisions

From DISI
Jump to navigation Jump to search
(Created page with "From his bestseller, 101 things to not forget <pre> This just came out of some debugging. Thought you may be interested. It will return the number of occurrences of a particu...")
 
No edit summary
Line 26: Line 26:


<pre>
<pre>
[[Category:SQL]]
[[Category:Internal]]
[[Category:Cheminformatics]]
[[Category:Cool scripts]]

Revision as of 04:22, 14 February 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;