Hi, ahh, great list!
thanks Richard and Thomas, I think both solutions solve the problem, anyway I went for Thomas' solution because it's without CUSTOM query stuff. Yes, the group by clause did the trick, I better go for my old SQL textbook ;-)) Thanks again, Merci, Gruesse, Tino -----Urspr�ngliche Nachricht----- Von: Thomas Fischer [mailto:[EMAIL PROTECTED] Gesendet: Montag, 22. November 2004 13:07 An: Apache Torque Users List Betreff: RE: Antwort: SQL->Criteria howto Hi Tino, (alternative solution, just got Richard's answer but decided to send this anyway) ok, again translated to the book example (obvious where clauses omitted) I think the following statement is what you need (obvious where clauses omitted, again translated to the book example (this is what I can test easily)): select book_id from book where author_id in (select max(author_id) from book group by title); which gives you the book with the maximal author_id for each distinct title If that does not work within torque, do it in two calls (in one transaction): select max(author_id) from book group by title; select book_id from book where author_id in(result of 1st query); Thomas > Sperlich, Tino" <[EMAIL PROTECTED]> schrieb am 22.11.2004 12:41:07: > Hi all, > > it may be a bit confusing, there are two > sequences SEND_SEQUENZ and just SEQUENZ. > > Indeed, I need to have the max(SEND_SEQUENZ) > for every distinct SEQUENZ I find, hence the > IN clause. > Here is a data example: > > VAVI_ID AUFT~ID SEQUENZ SEND~SEQUENZ > 88139 67537 2 1 > 91329 67537 2 2 > 91330 67537 1 1 > > only this should be selected: max(send_sequenz) from every sequenz > > VAVI_ID AUFT_ID SEQUENZ SEND_SEQUENZ > 91329 67537 2 2 > 91330 67537 1 1 > > any hints how to "carve" that into a criteria? > Thanks again, > Tino > > -----Urspr�ngliche Nachricht----- > Von: Richard Bayet [mailto:[EMAIL PROTECTED] > Gesendet: Montag, 22. November 2004 12:30 > An: Apache Torque Users List > Betreff: Re: Antwort: SQL->Criteria howto > > > Hi all, > > Thomas' answer may not be very accurate (it's simpler than Tino what > Tino's need), but "select vavi_id, max(sequenz) from voravis > where vavi_id = 67537" should be fine. > > Of course, if you (Tino) want the max(sequenz) for every distinct > vavi_id, you'll need the IN clause. > > Thomas Fischer a �crit : > > > > > > > > Hi Tino > > > > 1) I do not understand why you need the alias. Following sql works for me > > (on tutorial tables, on oracle) > > select * from book where book_id in (select max(book_id) from book); > > > > Thomas > > > > "Sperlich, Tino" <[EMAIL PROTECTED]> schrieb am 22.11.2004 > > 11:40:18: > > > > > >>Hi all, > >> > >>I'd like to express this SQL in criteria syntax, but smth. is still > > > > missing: > > > >>SELECT VORAVIS.VAVI_ID, VORAVIS.SEQUENZ > >>FROM VORAVIS > >>WHERE VORAVIS.AUFT_ID=67537 AND > >>VORAVIS.SEND_SEQUENZ > >>IN > >>( > >> SELECT MAX(va.SEND_SEQUENZ) > >> FROM VORAVIS va > >> WHERE va.AUFT_ID=VORAVIS.AUFT_ID AND va.SEQUENZ=VORAVIS.SEQUENZ > >> ) > >> > >>My main problem is how to create the table alias "va" in the IN clause. > >>Using the criteria.addAlias() method gives me "FROM VORAVIS, VORAVISva", > > > > i.e. > > > >>double table statements. > >>For the in clause I use an adjusted criteria class supporting the > >>addIn(column, criteria) operation, basically just writing "column IN > > > > query". > > > >>What am I missing? > >> > >>Thanks, > >>Tino > >> > >>--------------------------------------------------------------------- > >>To unsubscribe, e-mail: [EMAIL PROTECTED] > >>For additional commands, e-mail: [EMAIL PROTECTED] > >> > > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
