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]
