I'm having trouble formulating an HQL query in hibernate 2.1.

I have a data structure where a FileInformation can have many corresponding FileData.
A FileData has a version date that gets updated every time a new version of the file data is stored on the system.

What I want to do is fetch out a list of FileInformation along with the most recent version of the file data for each, which does not exceed a specified version date.

I've tried the following query, which fails because it doesn't like an aggragator in the where clause.

select
fileInfo,
fileData,
max(fileData.versionDate)
from
FileInformation as fileInfo,
FileData as fileData
where
fileData.fileInformation = fileInfo and
fileData.versionDate = max(fileData.versionDate)
group by
fileInfo
having
fileData.versionDate <= :versionDate



I've also tried this version, which fails with an exception: net.sf.hibernate.QueryException: , expected in SELECT.
I'm guessing that HQL does not allow aliasing of aggregate function results.

select
fileInfo,
fileData,
max(fileData.versionDate) maxDate
from
FileInformation as fileInfo,
FileData as fileData
where
fileData.fileInformation = fileInfo and
fileData.versionDate = maxDate
group by
fileInfo
having
fileData.versionDate <= :versionDate


Is there any other way to do this?

You need a subselect for this. I recommend reading a good book on SQL, since HQL is conceptually similar.