SQL Select row with max value in one column

Question

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?

asmgx · Accepted Answer · 2019-10-07 03:49:08Z

2

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

answered Oct 7, 2019 at 3:49

asmgx

8,15019 gold badges102 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aiden Over a year ago

what if I wanted to add another column (Pno for example) to the result. Is there a way to achieve that with the same query?

Collectives™ on Stack Overflow

SQL Select row with max value in one column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related