Re: New to SQL

rfrazier_at_mailstorm.dot.gov (Richard Frazier) wrote:
>KamiCASE (case_at_pt.hk-r.se) wrote:
>: Hi !
 

>: I have a problem. I'm trying to write a SQL statement to recive alla
>: customers names for the 6000 best customers who has bought articels
>: during 1995.
>

 [snip]
>
>The easiest way to do this is to simply set a totalamount limit where you know
>you will get at least your 6000 customers, then query based on that limit. For
>example:
>
>select a.name,b.totalamount
>from customer a, order b
>where a.customerid = b.customerid
>and b.totalamount > 100000; (this assumes at least 6000 customers have totals >
>100000. If you are not sure what limit to use, run some preliminary queries to
>help find the limit:
>

 [snip some more]
>

One problem with your solution is that if order contains one row for every order placed then you are probably going to get the same customer appearing multiple times in the result. Instead you might try:

 select distinct name from customer where customerid in (select distinct customerid    from orders where rownum<=6000 order by totalamount desc);

I haven't tried this but I think it should work. I don't think you can include the totalamount in the select because I don't believe that SQL will allow you to have more than one column in a select if you have a "distinct" clause.

Ken "Somewhat new to this myself" Denny Received on Sun Dec 10 1995 - 00:00:00 CET