Skip site navigation (1) Skip section navigation (2)

Re: Aggregate question

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
Cc: Postgres <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Aggregate question
Date: 2004-08-29 06:50:53
Message-ID: 1093762253.29613.316.camel@linda (view raw or flat)
Thread:
Lists: pgsql-novice
On Fri, 2004-08-27 at 11:54, Sean Davis wrote:
> 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;

...
> But, what I actually want is only those target_ids that reach the max  
> score like:

The way to restrict the GROUP BY output is with a HAVING clause.  I
think it should go something like this:

select oligo,target_id,max(2*matches-mismatch) as "score" from hit
natural join oligo where oligo like 'H200000%' group by oligo,target_id
HAVING max(2*matches-mismatch) = (SELECT max(2*matches-mismatch) from
hit);

I don't think you can use score as a column name in the having clause,
which is why I have repeated the original definition of that column.

-- 
Oliver Elphick                                          olly(at)lfix(dot)co(dot)uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Preach the word; be instant in season, out of season; 
      reprove, rebuke, exhort with all longsuffering and 
      doctrine."          II Timothy 4:2 


In response to

pgsql-novice by date

Next:From: operationsengineer1Date: 2004-08-29 06:55:46
Subject: Re: pgsql functions and transactions?
Previous:From: MikeDate: 2004-08-28 22:32:37
Subject: Re: pgsql functions and transactions?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group