From: boston103@hotmail.com   
      
   Hello,   
      
   SQL-92:   
      
   select distinct customernumber   
   from t1   
   where customernumber not in (select customernumber from t1 where   
   customerbranch is null)   
      
   SQL-99 (probably faster):   
      
   select distinct customernumber from   
     (select customernumber, first_value(customerbranch) over (partition by   
   customernumber order by customerbranch desc) first   
      from t1)   
   where first is not null   
      
      
   VC   
      
   "soni29"  wrote in message   
   news:cad7a075.0401261754.651428da@posting.google.com...   
   > hi,   
   > i need some help with a query, also to find out if this is even   
   > possible with sql.  currently i have a table with the following data:   
   >   
   > CustomerNumber     CustomerBranch   
   >     123 NULL   
   >     123 1   
   >     123 2   
   >     221 NULL   
   >     221 5   
   >     555 1   
   >     555 9   
   >     125 NULL   
   >   
   > now in this data, CustomerNumber and CustomerBranch are the keys, each   
   > customer MUST have a CustomerBranch with null, those NULL   
   > CustomerBranch's represent the company headquaters, while the ones   
   > with numbers are the other offices.  Occassionally data is missing, in   
   > the example above CustomerNumber 555 does not have a NULL   
   > CustomerBranch, this is wrong.  Is there anyway in SQL to find all   
   > those CustomerNumbers who do not have a null, there can only be one   
   > null per CustomerNumber.  i was thinking about using a   
   > count(CustomerBranch) but not sure how to write it to count all those   
   > CustomerBranchs per CustomerNumber that are equal to 0, if that's the   
   > right way to do it.   
   >   
   > Thank you.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)