AW: Antwort: SQL->Criteria howto

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