I imagine ORDER BY is part of it, but the harder piece is getting the last sequence per batchnumber and THEN return that collection of objects (see example results below).
Regular SQL would be something like "SELECT batchnumber, MAX(sequence as MAX_sequence) FROM thetable ORDER BY batchnumber" for JUST getting the last sequence number. You still have to do a JOIN or SUBSELECT from this query to attach to the actual search critera (item = "foo" or what-have-you). Then, the end result is still to return a collection of EJB local or remotes. Is there an elegant, object-oriented way of handling this kind of scenario? A 'J2EE' way of handling it? -D >>>> Subject: RE: [JBoss-user] Challenge for EJB-QL, JBossQL, or declared SQL? From: [EMAIL PROTECTED] Date: Thu, 6 Nov 2003 10:52:24 +0100 ORDER BY works in JBossQL. It is also in the EJB 2.1 spec. But, I believe JBoss 3.2.x currently supports 2.0. Regards, Harm de Laat Informatiefabriek The Netherlands >>>> "Stephane Nicoll" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 11/06/2003 09:53 AM I am not sure ORDER BY is allowed (what about caching then?) I know EJB QL does not allow that. Maybe JBoss QL. Regards, Stephane >>>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 9:24 AM Doesn't 'ORDER BY' help you? >>>> "Darren Hartford" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 11/05/2003 04:10 PM Hey all, I got an interesting problem that I should think some of you may have already come across in some fashion or another. I want to do a search to return a collection of objects, but it needs to be very specific as follows: Example table: batchnumber | sequence | receive_date | town | item | status with example values: "1",1,"11/5/2003","MONTREAL","widget", "INVENTORY" "1",2,"11/5/2003","MONTREAL","widget", "FRONT" "1",3,"11/5/2003","MONTREAL","widget", "INVENTORY" "2",1,"11/5/2003","MONTREAL","foo", "FRONT" "3",1,"11/5/2003","MONTREAL","bar", "FRONT" "3",2,"11/5/2003","MONTREAL","bar", "SOLD" "4",1,"11/5/2003","TORONTO","bar", "FRONT" "5",1,"11/6/2003","MONTREAL","bar", "INVENTORY" What I would like is the **last sequence-per-batchnumber** for results based on a search. example psuedo-query: SELECT OBJECT(o) WHERE ((o.town = "MONTREAL") AND (o.receive_date = "11/5/2003")) would return: "1",3,"11/5/2003","MONTREAL","widget", "INVENTORY" "2",1,"11/5/2003","MONTREAL","foo", "FRONT" "3",2,"11/5/2003","MONTREAL","bar", "SOLD" And another psuedo-query: SELECT OBJECT(o) WHERE (o.item = "bar") would return: "3",2,"11/5/2003","MONTREAL","bar", "SOLD" "4",1,"11/5/2003","TORONTO","bar", "FRONT" "5",1,"11/6/2003","MONTREAL","bar", "INVENTORY" Based on JBoss 3.2.2 and Mysql 3.23, could EJB-QL, JBossQL, or some type of declared SQL be able to solve this? An example pretty please! Thanks everyone! -D ------------------------------------------------------- This SF.net email is sponsored by: SF.net Giveback Program. Does SourceForge.net help you be more productive? Does it help you create better code? SHARE THE LOVE, and help us help YOU! Click Here: http://sourceforge.net/donate/ _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
