Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Statement Question

Re: SQL Statement Question

From: Michael G. Schneider <mgs_at_mgs-software.de>
Date: Sat, 27 Jan 2001 08:16:37 +0100
Message-ID: <94ts5q$b4u$01$1@news.t-online.com>

Thank's a lot for the answer. I tried and got a problem with the "NULL as ordamount" clause. Oracle complains about incompatible types. But please also have a look at my response to the other post in this thread.

Michael G. Schneider
mailto:mgs_at_mgs-software.de

"Klaus Zeuch" <KZeuchnospam_at_hotmail.com> schrieb im Newsbeitrag news:94rjk3$mgq$1_at_papyrus.erlm.siemens.de...
> One of many possible solutions:
>
> select distinct t1.custpk, t1.custname, t2.ordamount
> from customer t1, order t2
> where t1.custpk = t2.ordcustomer
> and t2.ordamount = (select max(ordamount)
> from order t3
> where t3.ordcustomer = t1.custpk)
> union all
> select t4.custpk, t4.custname, NULL as ordamount
> from customer t4
> where not exists (select 1
> from order t5
> where t5.ordcustomer = t4.custpk)
>
> Klaus
> Michael G. Schneider <mgs_at_mgs-software.de> schrieb in im Newsbeitrag:
> 94r9o6$ee9$01$1_at_news.t-online.com...
> > 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)
> >
> > 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
> >
> >
>
>
Received on Sat Jan 27 2001 - 01:16:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US