Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help With A Select
HornDog wrote:
>
> 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.
>
> I wrote the following query that returns '900', but how can I get the '5000'
> that goes along with it?
>
> SELECT route, MIN(available)
> FROM table_a
> WHERE route=11
> GROUP BY route;
>
> Any help would be appreciated ASAP!
>
> Brian
This query might work for you. Note that if you have multiple records that have the same min value, they will all be reported.
select a.*
from table_a a,
(select route,min(available) from table_a group by route) b where a.route = b.route and a.available = b.available;
-- Bob Swisshelm Eli Lilly and Company swisshelm_at_lilly.comReceived on Mon Jul 28 1997 - 00:00:00 CDT