Problems with GROUP BY

"Mike Deck" <MichaelBDeck@TexasHealth.org> wrote in message <news:3d4e9b23$1@10.1.10.29>...

> numbers, so the newest report in a case is the one with the highest version
> number. I need to write a query that returns the text of only the most recent
> reports on a given patient.
>
> I was thinking I could do something like:
>
> SELECT Reports.ReportText
> FROM Reports INNER JOIN Cases ON Reports.CaseID = Cases.CaseID
> WHERE Cases.PatientID = (search criteria)
> GROUP BY CaseID
> HAVING Reports.Version = MAX(Reports.Version)
>
> Unfortunately this won't work because I can't select ReportText without grouping
> by ReportText. Any help would be much appreciated.

Close! This should get you the most recent report per case:

select reports.caseid, reports.version,
max(reports.reporttext) as reporttext
from reports inner join reports as r on reports.caseid = r.caseid
group by reports.caseid, reports.version
having reports.version = max(r.version)

It shouldn't make any difference whether you use min() or max()
on Reports.ReportText. It's just to get the SQL parser to STFU.

Now add the Cases table back in:

select reports.caseid, reports.version,
max(reports.reporttext) as reporttext
from (reports inner join reports as r on reports.caseid = r.caseid)
inner join cases on reports.caseid = cases.caseid
where cases.patientid = bork bork bork
group by reports.caseid, reports.version
having reports.version = max(r.version)

--
Joe Foster <mailto:jlfoster%40znet.com> Space Cooties! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!