Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

dbf2mysql parameter, WWWXXXAPC, wwwxxxAPC, How to unsubscrube from dategen spam, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text

Links

XODOX
Impressum

#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