Re: query trigger

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