Re: SQL Statement Question

From: Michael G. Schneider <mgs_at_mgs-software.de>
Date: Sat, 27 Jan 2001 08:09:13 +0100
Message-ID: <94trnu$h80$05$1@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 Sat Jan 27 2001 - 01:09:13 CST