Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
In-Reply-To: <96F363742E3BAF4EBE5EA6A846DA14B417EF63@mail.hpc-hh.de>
Subject: RE: Antwort: SQL->Criteria howto
To: "Apache Torque Users List" <torque-user@db.apache.org>
Message-ID: 
 <OFD00CCA9B.8F3E2862-ONC1256F54.00419DD3-C1256F54.00429459@seitenbau.net>
From: Thomas Fischer <Fischer@seitenbau.net>
Date: Mon, 22 Nov 2004 13:07:12 +0100
MIME-Version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: quoted-printable


Hi Tino,
(alternative solution, just got Richard's answer but decided to send th=
is
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 t=
itle

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@hpc-hamburg.de> 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=FCngliche 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 =3D 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 =E9crit :
> >
> >
> >
> > 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=3D67537 AND
> >>VORAVIS.SEND_SEQUENZ
> >>IN
> >>(
> >> SELECT MAX(va.SEND_SEQUENZ)
> >> FROM VORAVIS va
> >> WHERE va.AUFT_ID=3DVORAVIS.AUFT_ID AND va.SEQUENZ=3DVORAVIS.SEQUEN=
Z
> >> )
> >>
> >>My main problem is how to create the table alias "va" in the IN cla=
use.
> >>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 I=
N
> >
> > 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
>=


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