I cannot figure out why this is not working. For some reason the
following statement is yielding multiple records with the same CESAFID
eventhough it is supposed to be grouped.

The query is intended to find the most recent record status date
(GoStatusDate) and record status (GoStatus).

Please help!

SELECT CESAFID, MAX(GoStatusDate) AS GoStatDate, GoStatus
FROM dbo.Go_Report
GROUP BY CESAFID, GoStatus

On Tue, 22 Apr 2008 08:11:07 -0700 (PDT), t8ntboy <t8ntboy [at] gmail.com>
wrote:

>I cannot figure out why this is not working. For some reason the
>following statement is yielding multiple records with the same CESAFID
>eventhough it is supposed to be grouped.
>
>The query is intended to find the most recent record status date
>(GoStatusDate) and record status (GoStatus).
>
>Please help!
>
>SELECT CESAFID, MAX(GoStatusDate) AS GoStatDate, GoStatus
>FROM dbo.Go_Report
>GROUP BY CESAFID, GoStatus

You patently have more than on GOStatus for the same CESAFID, so the
group by GoStatus is wrong.

You need something like

select CESAFID, gostatusdate, gostatus
from dbo.GO_Report a
where a.gostatusdate = (select max(b.gostatusdate) from dbo.go_report
b where a.cesafid = b.cesafid)