Re: SQL Statement Question

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 - 05:50:24 CST