Empty set returned from query

Question

Any help is greatly appreciated.

I have a table hospital:

Nurse + Year + No.Patients
A001 |2000 | 23
A001 |2001 | 30
A001 |2002 | 35

B001 |2000 | 12
B001 |2001 | 15
B001 |2002 | 45

C001 |2000 | 50
C002 |2001 | 59
C003 |2002 | 69
etc

What I am trying to do is work out which nurse had the greatest increase of patients for the years 2000 - 2002.

Clearly B001 did as her patients increased from 12 to 45 and increase of 33 and what I am trying to produce is the result B001 | 33.

This is what I have so far:

select a.nurse,a.nopats from hospital as a
  join
    ( select nurse,max(nopats)-min(nopats) as growth
        from hospital where year between 2000 and 2002 group by nurse ) as s1
  on a.nurse = s1.nurse and a.nopats = s1.growth
  where year between 2000 and 2002;

but all I get returned is an empty set.

I think I need an overall max(nopats) after the join.

Any help here would be great.

Thanks!

Answer 1

Try this:

SELECT nurse, (max(nopats) - min(nopats)) AS growth
  FROM hospital
 WHERE year BETWEEN 2000 AND 2002
 GROUP BY nurse
 ORDER BY growth DESC
LIMIT 1;

Result: B001 | 33 due to LIMIT 1; just leave it away if you want more results.

Answer 2

SELECT nurse, MAX(nopats) - MIN(nopats) AS Growth
  FROM hospital 
 WHERE year BETWEEN 2000 AND 2002
 GROUP BY nurse
 ORDER BY Growth

That should do it. Let me know if thats what you needed.