RE: how to write this sql query

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 17 Jun 2004 14:04:10 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKOEGAEOAA.mwf@rsiz.com>

not sure whether your data includes any possible ties, in which case you'll get only the first one.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Guang Mei Sent: Thursday, June 17, 2004 1:42 PM
To: Oracle-L-freelists
Subject: FW: how to write this sql query

Yes, It is working. Thanks!

Guang

> -----Original Message-----

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;

• ant

  ---

  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 - 13:07:11 CDT