RE: how to write this sql query

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Thu, 17 Jun 2004 13:28:35 -0400
Message-ID: <D17DB304A9F42B4787B68861F9DAE61C51D128@wgdc02.wgenhq.net>

Guang,
Add MAX(score) over() to your query and that should do it, so:

select DISTINCT QUERYID,
FIRST_VALUE(SUBJID) OVER (PARTITION BY QUERYID ORDER BY score DESC), MAX(score) OVER()
from blastresults where QUERYID=62;

-----Original Message-----

From: Guang Mei [mailto:gmei_at_incyte.com] Sent: Thursday, June 17, 2004 1:20 PM
To: Oracle-L-freelists
Subject: how to write this sql query

Running oracle 8173 on Sun

MT_at_rex-SQL> select QUERYID,SUBJID,score from blastresults where QUERYID=62; QUERYID SUBJID SCORE
---------- ---------- ----------

        62     646558        411
        62      55957        406
        62     463496        290
        62      11382       2167
        62       6279       1864
        62       7203       1784
        62       9663       1754


MT_at_rex-SQL> select DISTINCT QUERYID,
                FIRST_VALUE(SUBJID) OVER (PARTITION BY QUERYID ORDER BY

score DESC)
from blastresults where QUERYID=62;

QUERYID FIRST_VALUE(SUBJID)OVER(PARTITIONBYQUERYIDORDERBYSCOREDESC)
---------- -----------------------------------------------------------

        62                                                       11382

What I want from the above 7 rows is

62       --- queryid  (I already have , this is not a problem).
2167     --- max(score)
11382    --- subjid that corresposnding to max(score)

The second query does give me the subjid that corresponds to max(score), but
I cann't seem to be able to get the score value at the same time.

And I don't want to query the table twice. Any ideas?

TIA. Guang

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu Jun 17 2004 - 12:25:29 CDT