HUXiqAuN

1CREATE TABLE cmap5 (
2   name     varchar(2000),
3   lexemes  tsquery
4); 
5     
6CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn (
7   cid       int NOT NULL,  -- REFERENCES pubchem_compounds_index(cid)
8   name      varchar(2000) NOT NULL,  
9   synonym   varchar(2000) NOT NULL,
10   tsv_syns  tsvector,
11   PRIMARY KEY (cid, name, synonym)
12); 
13     
14SELECT s.cid, s.synonym, c.name, ts_rank(s.tsv_syns,c.lexemes,16) 
15FROM synonyms_all_gin_tsvcolumn s, cmap5 c
16WHERE c.lexemes @@ s.tsv_syns 
17     
18cid     |  synonym                              | name (query)              | rank
195474706 | 10-Methoxyharmalan                    | 10-methoxyharmalan        | 0.0901673
201416    | (+/-)12,13-EODE                       | 12,13-EODE                | 0.211562
215356421 | LEUKOTOXIN B (12,13-EODE)             | 12,13-EODE                | 0.211562
22 180933 | 1,4-Chrysenequinone                   | 1,4-chrysenequinone       | 0.211562
235283035 | 15-Deoxy-delta-12,14-prostaglandin J2 | 15-delta prostaglandin J2 | 0.304975
245311211 | 15-deoxy-delta 12 14-prostaglandin J2 | 15-delta prostaglandin J2 | 0.304975
255311211 | 15-deoxy-Delta(12,14)-prostaglandin J2| 15-delta prostaglandin J2 | 0.304975
265311211 | 15-Deoxy-delta-12,14-prostaglandin J2 | 15-delta prostaglandin J2 | 0.304975
275311211 | 15-Deoxy-delta 12, 14-Prostaglandin J2| 15-delta prostaglandin J2 | 0.304975 
28     
29SELECT DISTINCT ON (c.name)
30       c.name, min(s.synonym) AS min_synonym, s.cid
31     , ts_rank(s.tsv_syns, c.lexemes, 16) AS rnk
32     , count(*) AS ct
33FROM   synonyms_all_gin_tsvcolumn s
34JOIN   cmap5                      c ON c.lexemes @@ s.tsv_syns
35GROUP  BY c.name, rnk, s.cid
36ORDER  BY c.name, rnk DESC, ct DESC;