Re: SQL Statement Question

From: Klaus Zeuch <KZeuchnospam_at_hotmail.com>
Date: Fri, 26 Jan 2001 10:36:00 +0100
Message-ID: <94rjk3$mgq$1@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 Fri Jan 26 2001 - 03:36:00 CST