SQL Sub-Query - how to find a min value

Question

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

Answer 1

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.

Answer 2

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)

Answer 3

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.