From: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> |
---|---|
To: | Postgres <pgsql-novice(at)postgresql(dot)org> |
Subject: | Aggregate question |
Date: | 2004-08-27 10:54:03 |
Message-ID: | 690C1F3D-F817-11D8-AF80-000A95D7BA10@mail.nih.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-08-27 14:34:23 | Re: Storing a plan to disc... |
Previous Message | Katsaros Kwn/nos | 2004-08-27 10:50:02 | Storing a plan to disc... |