#1: Basic SQL Question
Posted on 2008-04-22 17:11:07 by 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
Report this message |
#2: Re: Basic SQL Question
Posted on 2008-04-22 17:26:05 by Iain Sharp
On Tue, 22 Apr 2008 08:11:07 -0700 (PDT), t8ntboy <t8ntboy@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)
Report this message |
