Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is this possible in SQL?

Re: Is this possible in SQL?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 19 Jul 2002 23:05:50 -0700
Message-ID: <92eeeff0.0207192205.18d0bc8c@posting.google.com>


dreamer33334_at_yahoo.com (Joe Imbrogo) wrote in message news:<65dcbf17.0207191718.1923e6be_at_posting.google.com>...
> Let us assume there is a view A with data as follows...
>
> X Y Z
> -----------------------
> 12 2 2
> 14 2 3
> 17 2 9
> 19 3 12
> 21 3 16
> 25 4 21
>
> I need get the value of X for every unique value of Y, where Z is the
> lowest in its range.
>
> For example, the result for the above example would be
>
> X Y
> -------------
> 12 2
> 19 3
> 25 4
>
> I know this is very easy to do in PL/SQL, but I wanted to take up the
> challenge of doing this is a regular SQL statment. However, I could
> not make much progress.
>
> Any help on this would be greatly appreciated.
>
> Thanks,
> Joe

One way is,

SELECT v1.X, v1.Y
FROM your_view v1, your_view v2
WHERE v1.X = v2.X
AND v1.Y = v2.Y
AND v1.Z = (SELECT MIN(Z)

            FROM your_view 
            WHERE Y = v1.Y)

/

//Rauf Sarwar Received on Sat Jul 20 2002 - 01:05:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US