Ring SQL query - Revision history

Frodo at 00:14, 11 March 2014

2014-03-11T00:14:27Z

← Older revision		Revision as of 00:14, 11 March 2014
Line 27:		Line 27:
	</pre>		</pre>

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

Frodo at 05:07, 14 February 2014

2014-02-14T05:07:43Z

← Older revision		Revision as of 05:07, 14 February 2014
Line 30:		Line 30:
	[[Category:Internal]]		[[Category:Internal]]
	[[Category:Cheminformatics]]		[[Category:Cheminformatics]]
	~~[[Category:Cool scripts]]~~

Frodo at 04:22, 14 February 2014

2014-02-14T04:22:44Z

← Older revision		Revision as of 04:22, 14 February 2014
Line 25:		Line 25:
	AND occurrences > 1;		AND occurrences > 1;

	<pre>		</pre>

	[[Category:SQL]]		[[Category:SQL]]

Frodo at 04:22, 14 February 2014

2014-02-14T04:22:06Z

← Older revision		Revision as of 04:22, 14 February 2014
Line 26:		Line 26:

	<pre>		<pre>

			[[Category:SQL]]
			[[Category:Internal]]
			[[Category:Cheminformatics]]
			[[Category:Cool scripts]]

Frodo: Created page with "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 particu..."

2014-01-29T20:43:05Z

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..."

New page

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 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;

<pre>