AW: Antwort: SQL->Criteria howto

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:Fischer@xxxxxxxxxxxxx]
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" <t.sperlich@xxxxxxxxxxxxxx> 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:bayet@xxxxxxxxxx]
> 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" <t.sperlich@xxxxxxxxxxxxxx> 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: torque-user-unsubscribe@xxxxxxxxxxxxx
> >>For additional commands, e-mail: torque-user-help@xxxxxxxxxxxxx
> >>
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@xxxxxxxxxxxxx
> > For additional commands, e-mail: torque-user-help@xxxxxxxxxxxxx
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@xxxxxxxxxxxxx
> For additional commands, e-mail: torque-user-help@xxxxxxxxxxxxx
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@xxxxxxxxxxxxx
> For additional commands, e-mail: torque-user-help@xxxxxxxxxxxxx
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@xxxxxxxxxxxxx
For additional commands, e-mail: torque-user-help@xxxxxxxxxxxxx