Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Urgent 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
>
> PS.
> What I need to see back from the query is:
>
> Available Capacity
>
> 900 5000
Use your original query as a subquery in a correlated subquery:
sql "select route ,available, capacity from table_a a where
available = (
SELECT MIN(available)
FROM table_a b
WHERE a.route=b.route
GROUP BY route)"
If you have many values of route, this will return a single row for each different route:
11 900 5000
13 500 19
You can always add a where clause to select specific routes.
-- Mike Gahan Information Systems Division University College London http://www.ucl.ac.uk/~ccaamrg/Received on Fri Jul 25 1997 - 00:00:00 CDT