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?