Help with Proper use of JB-QL MAX returning 1 complete entit

[websel]

Hi!

I firguring out how to write a finder method that returns one entity bean selected on a MAX creteria.

I have a report_id, and every update on the report, the version field is incremented. To get the latest report available in SQL it would be

SELECT MAX(version),(other fields needed) FROM Test WHERE Test.report_id = ..

I tried this in JB-QL but get stucked.

* @jboss.query
* query="SELECT OBJECT(o) FROM Test o WHERE o.report_id = ?1 AND o.version=MAX(o.version)"
* signature="java.util.Collection findByLatestVersionReport_id(int report_id)"

Something else i tried was:

SELECT OBJECT(o) FROM Test o WHERE o.report_id = ?1 AND o.version=( SELECT MAX(o.version) FROM Test o WHERE o.report_id=?1 )

Both queries won't work. Do i need to do this in two steps? like wrting two finders, 1st is looking up max(version) and that is reused in the 2nd finder to get teh report? I hope not.

Many thanks in advance,

Wessel de Roode

[websel]

I've found a workaround for now:

SELECT OBJECT(o) FROM Test o WHERE o.report_id =?1 ORDER BY o.log_id DESC LIMIT 1

Could anyone post a version which uses the MAX() function of JB-QL please?

Thanks,
Wessel de Roode