RE: SQL basic questions

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

The Distinct clause simply tells SQL to remove duplicate ROWS from the
result set.  It's not going to be able to identify WHICH distinct value you
want....  If you're looking to limit output to a single model# per account,
you will probably need to do something more creative, such as determining
the model# that each customer is buying most often.

This problem is difficult to accomplish in a single statement, but can be
accomplished somewhat (in)efficiently via a series of subselects.  For this,
I often use the "common table expression" syntax, which allows you to do all
sorts of cool summarization and filtering subselects...  

Here's one that I tested with...

with s1 (Cust, SKU, netQ, net$) as (                               
SELECT DSCST#, DSSKU, sum(DSQTY), sum(DSPRIC-DSDISC) 
FROM dslsales10         
WHERE dsdate between current_date - 1 years and 
                     current_date - 1 days 
GROUP BY DSCST#, DSSKU )                             
                                                                   
select cust, sku                                                   
from s1                                                            
where netq = (select max(x.netq)                                   
              from s1 x                                             
              where s1.cust=x.cust                                  
              group by x.cust)                                      

This statement will build a list of customers with the sku that they have
ordered the most qty.  This is based off a sales history file....  S1
returns sales by customer and sku, summarized to a single row.  We then use
that summary table in main select, selecting out the row for the customer
which represents their most purchased sku.  

hth,

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
steema@xxxxxxxxxxxxx
Sent: Monday, April 24, 2006 8:20 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL basic questions


HI I want to be able to use the Select Distinct feature on a key level.
I have a file w/ Acct and model #, and I want to list only one model per
acct. instead when i do Select Distinct currently, I get many per
Acct/model. Is there a way to make this break on the key?