Re: query trigger

andrewst wrote:

> Originally posted by Drukqs
> > andrewst wrote in news:2900872.1053435874_at_dbforums.com:"]news:2-
> > 900872.1053435874_at_dbforums.com:[/url]
> >
> > > Forms always lags behind with improvements to SQL. The ability
> > to refer
> > > to column aliases in the ORDER BY clause is not supported by
> > your
> > > version of Forms. Do this instead:
> > >
> > > SELECT pcnr, sum(number) AS n
> > > into :testblock.pc, :testblock.number
> > > FROM orders
> > > GROUP BY pcnr
> > > ORDER BY sum(number) desc;
> > >
> > > Your next problem will be that you are sure to raise a
> > TOO_MANY_ROWS
> > > exception, since INTO can only be used for single-row queries,
> > whereas
> > > the ORDER BY clause suggests you expect to get more than 1 row
> > back!
> >
> > What query do you suggest to just show the pcnr and number of the
> > most sold
> > pc. I have one pc in the db that is sold 5000 times and another
> > time it is
> > sold 8000 times. Now I like to show in 2 fields. Pc1 sold 13000 times.
> > Thx for the responce.
> If it is the same PC with the same PCNR, then the group by in the query
> will sum the 5000 and 8000 to 13000. There are various ways to get the
> top selling PC only, e.g.
>
> SELECT pcnr, n
> INTO :testblock.pc, :testblock.number
> FROM
> (
> SELECT pcnr, sum(number) AS n
> FROM orders
> GROUP BY pcnr
> ORDER BY sum(number) desc
> )
> WHERE ROWNUM = 1;
>
> --
> Posted via http://dbforums.com

The ORDER BY in an in-line view accomplishes what?

I mean other than wasting CPU.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)