Aggregate question

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)

Responses

Browse pgsql-novice by date

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