SQL Select row with max value in one column

I have the following query

SELECT Dnumber, SUM(WORKS_ON.Hours) AS sum_hours 
FROM DEPARTMENT 
JOIN PROJECT ON DEPARTMENT.Dnumber = PROJECT.Dnum 
JOIN WORKS_ON ON PROJECT.Pnumber = WORKS_ON.Pno 
GROUP BY Dnumber

RESULT:

Dnumber   sum_hours
--------------------
1           25.0
4          115.0
5          150.0

I want to select the row with the MAX sum_hours. I tried using

SELECT s.Dnumber, MAX(s.sum_hours)
FROM 
    (SELECT Dnumber, SUM(WORKS_ON.Hours) sum_hours 
     FROM DEPARTMENT 
     JOIN PROJECT ON DEPARTMENT.Dnumber = PROJECT.Dnum 
     JOIN WORKS_ON ON PROJECT.Pnumber = WORKS_ON.Pno 
     GROUP BY Dnumber) s 
GROUP BY s.Dnumber

But this returns the exact same result (all three rows).

Any idea how I can achieve this?

Answer

try this

SELECT TOP 1 Dnumber, SUM(WORKS_ON.Hours) sum_hours 
FROM DEPARTMENT 
JOIN PROJECT ON DEPARTMENT.Dnumber = PROJECT.Dnum 
JOIN WORKS_ON ON PROJECT.Pnumber = WORKS_ON.Pno GROUP BY Dnumber
ORDER BY SUM(WORKS_ON.Hours) DESC

Advertisement

Answer