I have created a subquery as follows
select hospital.hospitalcode, name, wardno, annualbudget
from hospital, ward
where hospital.hospitalcode = ward.hospitalcode
The question I am trying to answer is this: Taking account of all hospital wards, which hospital has the ward with the lowest annual budget? You should display the hospital code and its name, the ward number and its annual budget.
How would I find a single entry for this question? I realise I need to use MIN but do not know where to use this when using multiple column names
The more efficient approach is generally to use analytic functions
SELECT hospitalcode,
name,
ward,
annualbudget
FROM (SELECT h.hospitalcode,
h.name,
w.wardno,
w.annualbudget,
rank() over (order by w.annualbudget asc) rnk
FROM hospital h
JOIN ward w
ON (h.hospitalcode = w.hospitalcode))
WHERE rnk = 1
You can also, though, use a subquery
SELECT h.hospitalcode,
h.name,
w.wardno,
w.annualbudget
FROM hospital h
JOIN ward w
ON (h.hospitalcode = w.hospitalcode)
WHERE w.annualbudget = (SELECT MIN(annualbudget)
FROM ward)
Both of these methods will return multiple rows if there are multiple wards that are tied for the lowest budget. With the analytic function method, you can use the row_number function rather than rank to arbitrarily break the tie if you want to return exactly 1 row every time.
First, you want to write your query using proper join syntax:
select hospital.hospitalcode, name, wardno, annualbudget
from hospital join
ward
on hospital.hospitalcode = ward.hospitalcode
Second, you actually don't need the min function. You can use row_number():
select hospitalcode, name, wardno, annualbudget
from (select hospital.hospitalcode, name, wardno, annualbudget,
row_number() over (order by annualbudget) as seqnum
from hospital join
ward
on hospital.hospitalcode = ward.hospitalcode
) t
where seqnum = 1
This assigns a sequential number by order of the annual budget. So the smallest one has the value 1.
You can also do this by ordering by the annualbudget and just taking the first row. However, I like this method since it is a good introduction to windows functions.
First of all, you should change your query to use proper explicit joins, even if it is just for clarity. For your specific question, this is a way to do it:
SELECT H.hospitalcode, H.name, W.wardno, W.annualbudget
FROM Hospital H
INNER JOIN Ward W
ON H.hospitalcode = W.hospitalcode
WHERE W.annualbudget = (SELECT MIN(annualbudget) FROM Ward)
