Re: SQL Statement Question

Unfortunately the version is 8.05, but that statement looks interesting. I should have a look at the new SQL features. However, probably they won't help me with my 8.05 problem.

Michael G. Schneider
mailto:mgs_at_mgs-software.de

"Klaus Zeuch" <KZeuchnospam_at_hotmail.com> schrieb im Newsbeitrag news:953ok0$ld9$1_at_papyrus.erlm.siemens.de...
> As you didn't specify the version of Oracle, I assume 8.1.6 or above:
>
> select custpk, custname, ordpk, ordinfo, ordamount from (
> select c.custpk, c.custname, o.ordpk, o.ordinfo, o.ordamount
> , row_number() over (partition by c.custpk order by o.ordamount desc) as
> rang
> from t_Customer c, t_Order o
> where c.custPk = o.ordCustomer (+)
> ) where rang = 1;
>
> Klaus
>
> Michael G. Schneider <mgs_at_mgs-software.de> schrieb in im Newsbeitrag:
> 94trnu$h80$05$1_at_news.t-online.com...
> > Thank's a lot for the answer. The solution does work. However, I was not
> > precisely enough in my first description. The problem is:
> >
> > When selecting MAX(ordAmount) I will get the maximum amount, but I will
 not
> > get the order's primary key or some other fields that I might be
 interested
> > in (such as "order date" or "order info"). Can your statement be
 modified
 to
> > include other fields, too?
> >
> > Here is some SQL for creating and filling the tables:
> >
> > DROP TABLE T_Customer;
> > DROP TABLE T_Order;
> >
> > CREATE TABLE T_Customer
> > (
> > custPk NUMBER(6),
> > custName CHAR(30)
> > );
> >
> > CREATE TABLE T_Order
> > (
> > ordPk NUMBER(6),
> > ordCustomer NUMBER(6),
> > ordInfo CHAR(30),
> > ordAmount NUMBER(6)
> > );
> >
> >
> > INSERT INTO T_Customer(custPk,custName) VALUES(1,'Oracle');
> > INSERT INTO T_Customer(custPk,custName) VALUES(2,'Microsoft');
> > INSERT INTO T_Customer(custPk,custName) VALUES(3,'IBM');
> >
> > INSERT INTO T_Order(ordPk,ordCustomer,ordInfo,ordAmount)
> > VALUES(11,1,'Oracle ONE', 100);
> > INSERT INTO T_Order(ordPk,ordCustomer,ordInfo,ordAmount)
> > VALUES(12,1,'Oracle TWO', 200);
> > INSERT INTO T_Order(ordPk,ordCustomer,ordInfo,ordAmount)
> > VALUES(13,1,'Oracle THREE', 300);
> >
> > INSERT INTO T_Order(ordPk,ordCustomer,ordInfo,ordAmount)
> > VALUES(21,2,'Microsoft ONE', 500);
> > INSERT INTO T_Order(ordPk,ordCustomer,ordInfo,ordAmount)
> > VALUES(22,2,'Microsoft TWO', 400);
> > INSERT INTO T_Order(ordPk,ordCustomer,ordInfo,ordAmount)
> > VALUES(23,2,'Microsoft THREE', 500);
> >
> > And the result set should look like:
> >
> > custPk custName ordPk ordInfo ordAmount
> > 1 Oracle 13 Oracle THREE 300
> > 2 Microsoft 21 Microsoft ONE 500
> > 3 IBM NULL NULL NULL
> >
> > Of course, also the other Microsoft order with Amount=500 could have
 been
> > given.
> >
> > Michael G. Schneider
> > mailto:mgs_at_mgs-software.de
> >
> >
> >
> >
> >
> > "Alex Filonov" <afilonov_at_pro-ns.net> schrieb im Newsbeitrag
> > news:94sbtb$1ha$1_at_nnrp1.deja.com...
> > > In article <94r9o6$ee9$01$1_at_news.t-online.com>,
> > > "Michael G. Schneider" <mgs_at_mgs-software.de> wrote:
> > > > Suppose there is a Customer table containing
> > > > custPk (primary key)
> > > > custName (name)
> > > > and a Order table containing
> > > > ordPk (primary key)
> > > > ordCustomer (reference to Customer)
> > > > ordAmount (amount)
> > >
> > > select c.custName, max(c.ordAmount)
> > > from Customer c, Order o
> > > where c.custPk = o.ordCustomer (+)
> > > group by c.custName, c.custPk, o.ordCustomer
> > >
> > > >
> > > > Now I would like to select all Customers with their highest order.
 So
 there
> > > > should be exactly one row for each customer. And if that customer
 has
> > > > orders, only the highest one should be taken.
> > > >
> > > > How would I do this in SQL?
> > > >
> > > > Michael G. Schneider
> > > > mailto:mgs_at_mgs-software.de
> > > >
> > > >
> > >
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> >
>
>
Received on Mon Jan 29 2001 - 09:24:52 CST