How to use MAX function in SQL?

Question

This is my query:

SELECT Cottage15.CNUM,Cottage15.Rent,Assignment15.Hours
FROM Cottage15
INNER JOIN Assignment15
ON Cottage15.CNUM=Assignment15.CNUM
ORDER BY Assignment15.AID;

It works, but I can't figure out how to apply the Max function to hours? I was hoping that MAX(Assignment15.Hours) would work!?

This query shows all of the rows, but I only want it to show me certain rows that have the highest number based on the hour field.

Answer 1

you must add group aggregation to apply Max function

 SELECT Cottage15.CNUM,Cottage15.Rent,Max(Assignment15.Hours)
 FROM Cottage15
 INNER JOIN Assignment15
 ON Cottage15.CNUM=Assignment15.CNUM
 group by Cottage15.CNUM,Cottage15.Rent
 ORDER BY Assignment15.AID;

Answer 2

I think this is what you want

SELECT Cottage15.CNUM,Cottage15.Rent,Assignment15.Hours
FROM Cottage15
INNER JOIN Assignment15
ON Cottage15.CNUM=Assignment15.CNUM
WHERE Assignment15.Hours = (SELECT MAX(Assignment15.Hours) FROM Assignment15)
ORDER BY Assignment15.Hours

Answer 3

if you want to get max(hour) per each Cottage15.CNUM and Cottage15.Rent then use max(Assignment15.Hours) and group by Cottage15.CNUM,Cottage15.Rent:

SELECT Cottage15.CNUM,Cottage15.Rent,MAX(Assignment15.Hours)
FROM Cottage15
INNER JOIN Assignment15
ON Cottage15.CNUM=Assignment15.CNUM
GROUP BY Cottage15.CNUM,Cottage15.Rent

But if you want to get Cottage15.CNUM and Cottage15.Rent which have max(Assignment15.Hours) in the whole result set then you can get it by:

SELECT Cottage15.CNUM,Cottage15.Rent,Assignment15.Hours
FROM Cottage15
INNER JOIN Assignment15
ON Cottage15.CNUM=Assignment15.CNUM
WHERE Assignment15.Hours = ( SELECT MAX(Assignment15.Hours)
                             FROM Cottage15
                             INNER JOIN Assignment15
                             ON Cottage15.CNUM=Assignment15.CNUM
                            )