Re: Is this possible in SQL?

dreamer33334_at_yahoo.com (Joe Imbrogo) wrote in message news:<65dcbf17.0207200951.5fe182a7_at_posting.google.com>...
> 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

Try this instead,

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

              FROM your_view v2 
              WHERE v2.Y = v1.Y)

Regards
//Rauf Sarwar Received on Sun Jul 21 2002 - 10:14:04 CDT