Re: need aid with this query

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" <soni29_at_hotmail.com> wrote in message news:cad7a075.0401261754.651428da_at_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.
Received on Tue Jan 27 2004 - 03:37:30 CET