Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is this possible in SQL?
Thanks Rauf, that does work perfect. But, there is another caveat. The
view itself is created as result of a highly complex computation. When
I use that view three times, the performance is a bit bad. Is it
possible to select these items with a simpler query? Since this is
realtime data, using materialized views are out of question.
Thanks.
rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0207192205.18d0bc8c_at_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 - 12:51:56 CDT