gistfile1.sql

SELECT tmp.*,
  td.protein_accession AS pacc,
  a.doc_id,
  confidence_score  AS conf,
  relationship_type AS rel,
  a.description       AS descr
FROM
  (SELECT assay_id,
    COUNT(molregno) AS nmol,
    standard_type   AS stype
  FROM activities
  GROUP BY assay_id,
    standard_type
  ORDER BY nmol DESC
  ) tmp,
  assay2target a2t,
  assays a,
  target_dictionary td
WHERE nmol               > 75
AND nmol                 < 500
AND a.assay_id           = tmp.assay_id
AND a.assay_type         = 'B'
AND tmp.assay_id         = a2t.assay_id
AND a2t.assay_tax_id     = 9606
AND a2t.confidence_score >= 8
AND td.tid = a2t.tid;