1 Reply Latest reply on Apr 6, 2004 9:31 AM by Wessel de Roode

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

    Wessel de Roode Newbie


      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