how to write this sql query
- From: "Guang Mei" <gmei@xxxxxxxxxx>
- To: "Oracle-L-freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Jun 2004 13:19:43 -0400
Running oracle 8173 on Sun
MT@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@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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
