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]
