Re: query help

From: Urs Metzger <metzguar_at_yahoo.de>
Date: 26 Jul 2004 04:54:21 -0700
Message-ID: <4dd06112.0407260354.6251c180@posting.google.com>

gordon_t_wu_at_yahoo.com (Tao) wrote in message news:<5db74e62.0407231741.3ad3b72f_at_posting.google.com>...
> Hi experts,
>
> I know its probably a simple query, but I've spent a long time on it
> and could not figure it out.
>
>
> I'm trying to select distinct fieldA in the table, such that fieldB
> is smallest within each fieldA. Suppose the table has following rows:
>
> P_Key fieldA fieldB
> 1 1 5
> 2 1 4
> 3 2 4
> 4 2 3
>
> The result of the query would look like:
>
> P_Key fieldA fieldB
> 2 1 4
> 4 2 3
>
> Appreciate your time
>
> FA

Analytics rock:

SELECT DISTINCT FIRST_VALUE(el1) OVER(PARTITION BY el2 ORDER BY el3) el1,
                FIRST_VALUE(el2) OVER(PARTITION BY el2 ORDER BY el3) el2,
                FIRST_VALUE(el3) OVER(PARTITION BY el2 ORDER BY el3) el3
           FROM TEST;

Look at autotrace output: less consistent gets, less sorts

Urs Received on Mon Jul 26 2004 - 06:54:21 CDT