ca scm Questions

I am running a query that returns multiple rows using HSQL. I only want the most current row so I added a group by statement and a MAX statement for the date/time. In problem resolution I have found that the group by is working but the MAX statement is giving me an error. Any ideas?

Query below

select harenvironment.environmentname,
harstate.statename,
harpackage.packagename,
harallusers.username,
harallusers.realname,
haritems.itemobjid,
haritems.itemname,
harpathfullname.pathfullname,
max(harversions.creationtime)

from harpackage

Inner Join harenvironment
on harenvironment.envobjid = harpackage.envobjid

Inner Join harstate
on harstate.stateobjid = harpackage.stateobjid

Inner Join harallusers
on harallusers.usrobjid = harpackage.creatorid

Inner Join harversions
on harversions.packageobjid = harpackage.packageobjid

Inner Join haritems
on haritems.itemobjid = harversions.itemobjid

Inner Join harpathfullname
on harpathfullname.itemobjid = haritems.parentobjid

WHERE HARENVIRONMENT.ENVISACTIVE = 'Y'

AND ( HARENVIRONMENT.ENVIRONMENTNAME LIKE 'EDW%'
OR HARENVIRONMENT.ENVIRONMENTNAME LIKE 'EMD%'
OR HARENVIRONMENT.ENVIRONMENTNAME LIKE 'HFDR%'
OR HARENVIRONMENT.ENVIRONMENTNAME LIKE 'MCWET%'
OR HARENVIRONMENT.ENVIRONMENTNAME LIKE 'FRED%' )

AND ( SUBSTR(HARPACKAGE.PACKAGENAME,4,10) = '_20120914_'
OR SUBSTR(HARPACKAGE.PACKAGENAME,5,10) = '_20120914_'
OR SUBSTR(HARPACKAGE.PACKAGENAME,6,10) = '_20120914_'
OR SUBSTR(HARPACKAGE.PACKAGENAME,7,10) = '_20120914_' )

GROUP BY harenvironment.environmentname,
harstate.statename,
harpackage.packagename,
harallusers.username,
harallusers.realname,
haritems.itemobjid,
haritems.itemname,
harpathfullname.pathfullname

ORDER BY haritems.itemobjid,
harversions.creationtime desc,
haritems.itemname,
harpackage.packagename;

Does anyone have any experiance or heard of anyone integrating BMC Remedy Service Desk with CA-SCM?