how to write this sql query

From: Guang Mei <gmei_at_incyte.com>
Date: Thu, 17 Jun 2004 13:19:43 -0400
Message-ID: <NJEDKDKJDGAKAEKKNEEJOECDDFAA.gmei@incyte.com>

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

---

Received on Thu Jun 17 2004 - 12:17:40 CDT