SQL: Combining sum and max aggregate function

Question

How do I get the max value from the following query:

select sum(hours) from works_on group by pno;   
+------------+
| sum(hours) |
+------------+
|      52.50 |
|      50.00 |
|      55.00 |
|      25.00 |
|      55.00 |
+------------+

What I want is:

|      55.00 |
|      55.00 |

Thanks in advance.

Answer 1

Use Having Clause and Sub-query. Something like this

SELECT Sum(hours) 
FROM   works_on 
GROUP  BY pno 
HAVING Sum(hours) = (SELECT Sum(hours) h 
                     FROM   works_on 
                     GROUP  BY pno 
                     ORDER  BY h DESC 
                     LIMIT  1) 

But it is really easy in SQL SERVER where we have TOP 1 with Ties which avoids the sub-query

Answer 2

If you just want the maximum value, then one row will do:

select sum(hours)
from works_on
group by pno
order by sum(hours) desc
limit 1;

I'm not certain why you would want two rows, but then Prdp's answer is the right one.