Aggregate question
От | Sean Davis |
---|---|
Тема | Aggregate question |
Дата | |
Msg-id | 690C1F3D-F817-11D8-AF80-000A95D7BA10@mail.nih.gov обсуждение исходный текст |
Ответы |
Re: Aggregate question
(Oliver Elphick <olly@lfix.co.uk>)
|
Список | pgsql-novice |
I'm sorry for the simple question, but.... I have a table (description given below) that I want to do something like: select oligo,target_id,max(2*matches-mismatch) as "score" from hit natural join oligo where oligo like 'H200000%' group by oligo,target_id; oligo | target_id | score ------------+---------------------------------+------- H200000001 | ENST00000286479 | 138 H200000001 | gi|4557782|ref|NM_000015.1| | 138 H200000005 | ENST00000206765 | 138 H200000005 | gi|4507474|ref|NM_000359.1| | 138 H200000006 | ENST00000262093 | 138 H200000006 | gi|4557592|ref|NM_000140.1| | 138 H200000007 | ENST00000287225 | 138 H200000007 | ENST00000327775 | 96 <---I don't want this H200000007 | gi|4504012|ref|NM_000170.1| | 138 H200000008 | ENST00000278888 | 138 H200000008 | gi|23397640|ref|NM_000139.2| | 138 H200000010 | ENST00000309399 | 138 H200000010 | gi|6806892|ref|NM_000595.2| | 138 But, what I actually want is only those target_ids that reach the max score like: oligo | target_id | score ------------+---------------------------------+------- H200000001 | ENST00000286479 | 138 H200000001 | gi|4557782|ref|NM_000015.1| | 138 H200000005 | ENST00000206765 | 138 H200000005 | gi|4507474|ref|NM_000359.1| | 138 H200000006 | ENST00000262093 | 138 H200000006 | gi|4557592|ref|NM_000140.1| | 138 H200000007 | ENST00000287225 | 138 H200000007 | gi|4504012|ref|NM_000170.1| | 138 H200000008 | ENST00000278888 | 138 H200000008 | gi|23397640|ref|NM_000139.2| | 138 H200000010 | ENST00000309399 | 138 H200000010 | gi|6806892|ref|NM_000595.2| | 138 I just can't seem to quite get it. Thanks, Sean Table description: Table "public.hit" Column | Type | Modifiers -------------+-------------- +---------------------------------------------- hit_id | integer | not null default nextval('hit_pk_seq'::text) analysis_id | integer | matches | integer | mismatch | integer | repmatch | integer | n | integer | q_gap_count | integer | q_gap_bases | integer | t_gap_count | integer | t_gap_bases | integer | strand | character(1) | oligo_id | integer | qsize | integer | qstart | integer | qend | integer | target_id | text | tsize | integer | tstart | integer | tend | integer | block_count | integer | block_sizes | text | qstarts | text | tstarts | text | Indexes: "hit_pkey" primary key, btree (hit_id) "analysis_id" btree (analysis_id) "hit2" btree (oligo_id) "hit_analysis_id" btree (analysis_id) "oligo_id" btree (oligo_id, analysis_id, target_id, tstart, tend)
В списке pgsql-novice по дате отправления: