is Max() not counted as a aggregation function?

Question

In this query I am trying to get a list of distinct part numbers and the last date their status was updated. This is a working query in Access using the last() function. Since last() is only available in SQL Sever, I figured max() would do the dame thing. I have the following query in SSMS:

SELECT 
    max(DishSql2.dbo.tblMInvDetail.InvDate) AS LastOfInvDate, 
    DishSql2.dbo.tblMInvDetail.PartNo`
FROM DishSql2.dbo.tblMInvDetail
GROUP BY DishSql2.dbo.tblMInvDetail.PartNo
ORDER BY DishSql2.dbo.tblMInvDetail.InvDate DESC , DishSql2.dbo.tblMInvDetail.PartNo;

Results in :

Msg 8127, Level 16, State 1, Line 5

Column "DishSql2.dbo.tblMInvDetail.InvDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

There are only two columns, one is in the group by and and other is in the max() function. Can anyone what I'm doing wrong? Is there a better way to do this?

Answer 1

Msg 8127, Level 16, State 1, Line 5

Column "DishSql2.dbo.tblMInvDetail.InvDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

You're trying to order on the un-aggregated DishSql2.dbo.tblMInvDetail.InvDate column. What you probably want is:

SELECT 
    max(DishSql2.dbo.tblMInvDetail.InvDate) AS LastOfInvDate, 
    DishSql2.dbo.tblMInvDetail.PartNo`
FROM DishSql2.dbo.tblMInvDetail
GROUP BY DishSql2.dbo.tblMInvDetail.PartNo
ORDER BY max(DishSql2.dbo.tblMInvDetail.InvDate) DESC , DishSql2.dbo.tblMInvDetail.PartNo;