I need a finder to return lowest value of a column in a db t

[joe543]

Hi,

I'd like to be able to select the lowest value of a column out of all the rows in a db table. I know something like this can be done ...

'select min(column_name) from table_name;'

I am looking to use CMP here so how would I create a finder to execute the above select?

(is it even possible to do this using CMP?)

Thanks
Joe

[davidjencks]

I think you'd specify the where clause like this:

column_name = (select min(t.column_name) from table_name t)

Note you need the alias t for the nested subquery, and the finder should return a collection.

[joe543]

I gather this would be defined in a deployment descriptor - correct?

Where do I go to find out how to set this up? (i.e. is this part of ejb-ql or is it pre 2.0?)

joe

[davidjencks]

Its in cmp 1 and 2 both, I think the both free and for pay docs have a good discussion, and there are examples on the website I think.

[markd]

Look at http://www.jboss.org/online-manual/HTML/ch05s07.html

[joe543]

Hi,

I managed to get it to work using the folowing in my application's jaws.xml:
<enterprise-beans>

<ejb-name>CDBean</ejb-name>

findLeastHit
where id = (select min(t.id) from cdbean t)



</enterprise-beans>


The above was simply a 'proof-of-concept'. Thanks for the help!

When would it be appropriate to utilize a tag (as opposed to including the clause within the query)?

Joe

[davidjencks]

I might have made up the where tag or it might be cmp2 only. My advice was from memory, not recent use.