Basic SQL Question

Basic SQL Question

am 22.04.2008 17:11:07 von t8ntboy

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

Re: Basic SQL Question

am 22.04.2008 17:26:05 von Iain Sharp

On Tue, 22 Apr 2008 08:11:07 -0700 (PDT), t8ntboy
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)