Re: SQL Statement Question

From: Alex Filonov <afilonov_at_pro-ns.net>
Date: Mon, 29 Jan 2001 22:46:31 GMT
Message-ID: <954rs4$lqt$1@nnrp1.deja.com>

Try this:

select c.custName, o.ordPk, o.ordAmount
from Customer c,

     (select oo.ordPk, oo.ordAmount, oo.ordCustomer
      from Order oo,
	(select o1.ordCustomer, max(o1.ordAmount)
	 from Order o1
	  group by ordCustomer) o2
      where oo.ordCustomer = o2.ordCustomer
	and oo.ordAmount = o2.ordAmount) o

where c.custPk = o.ordCustomer (+)

Two possible problems with this query:

1. It returns more than 1 row for customers where more than 1 order has amount equal to max(ordAmount). You can remove those duplicates if you add group by c.custName, o.ordAmount to the end of the statement and take min(o.ordPk) in select clause.
2. It can be slow. In this case, it can be converted into SQL statement with 2 subqueries in where clause. It's going to be bigger and a little big ugly.

In article <9541h4$3m9$04$1_at_news.t-online.com>,   "Michael G. Schneider" <mgs_at_mgs-software.de> wrote:
> 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/
> > >
> > >
> >
> >
>
>

Sent via Deja.com
http://www.deja.com/ Received on Mon Jan 29 2001 - 16:46:31 CST