Hi everyone,

I've got a problem formulating a HSQL query.
This query works in normal SQL:

SELECT * FROM item, (SELECT id AS sub1,max(version) AS sub2 FROM item GROUP BY id) WHERE id=sub1 AND version=sub2;

An item has a composite primary key (id and version).
I want to get the most current items, thus items with the highest version.

I've tried to convert this SQL query to HSQL like this:

SELECT it FROM Item it,
(SELECT id.id AS sub1,max(id.version) AS sub2 FROM Item GROUP BY id.id)
WHERE it.id.id=sub1 AND it.id.version=sub2

But it doesn't work:
QuerySyntaxException: unexpected token: ( near line 2, column 1

Is it possible to use subselects like this?

Bye
Markus

Hibernate version: 3.2.1

Sorry, I overread the part in the docs which say: "Note that HQL subqueries may occur only in the select or where clauses."