Re: Urgent help on SQL

From: Eric & Carole Kamradt <kamradt_at_inconnect.com>
Date: 1997/08/05
Message-ID: <33E77AE8.8A8F527B@inconnect.com>#1/1

Faster might be --
example 1 - the index on table_a index on available is called table_a$available

select /*+ INDEX_ASC(a table_a$available) */

    a.route, a.available, a.capacity
from table_a a
where a.route = 11

and       rownum = 1
and     a.available is not null;

example 2 - make an index on both (route, available) the the first record found

                    (if available is not null) will return the

min(available)

select /*+ INDEX_ASC(a table_a$route_available) */

    a.route, a.available, a.capacity
from table_a a
where a.route = 11
and rownum = 1;

Jayadas C wrote:

> try this ...
>
> select a.route, a.available, a.capacity
> from table_a a
> where a.route = 11
> and rownum < 1
> and a.available = ( select min(b.available)
> from table_a b
> where b.route = a.route
> group by b.route
> );
>
> hope this solves your problem ... pls reply
>
> regards,
> jayadas
>
> > > >>Given the following table
> > > >>
> > > >>table_a
> > > >>
> > > >>route available capacity
> > > >>
> > > >>11 1200 3000
> > > >>11 1400 2000
> > > >>11 900 5000
> > > >>
> > > >>I need a query that will return a single row that has the
 minimum
> > > >>"available"
> > > >>value of the three rows and the corresponding "capacity".
> > > >>

--
eric kamradt
hm: (801) 567-0912
mailto: kamradt_at_inconnect.com
http://www.inconnect.com/~kamradt/resume.html

Received on Tue Aug 05 1997 - 00:00:00 CDT