db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sperlich, Tino" <t.sperl...@hpc-hamburg.de>
Subject AW: Antwort: SQL->Criteria howto
Date Mon, 22 Nov 2004 11:41:07 GMT
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@enseirb.fr]
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@hpc-hamburg.de> 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@db.apache.org
>>For additional commands, e-mail: torque-user-help@db.apache.org
>>
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message