"Omnibuzz" wrote:

> If you are using SQL Server 2005, then you can use the dense_rank() function,
> Much simpler..
>
> select a.ContactAddressId,a.contactid, dense_rank() over(order by a.Contactid)
> from tbl a
>
> --
> -Omnibuzz (The SQL GC)
>
> http://omnibuzz-sql.blogspot.com/
>
>
>
> "Omnibuzz" wrote:
>
> > slow performance..
> > you need an index on ContactId..
> > You can't do without the self join... So I guess its your call :)
> > --
> > -Omnibuzz (The SQL GC)
> >
> > http://omnibuzz-sql.blogspot.com/
> >
> >
> >
> > "Michael Maes" wrote:
> >
> > > Hi Omnibuzz,
> > >
> > > Thanks for your input.
> > > The result is indeed what i needed.
> > > The only downside is slow performance (six seconds for only 2.700 records).
> > >
> > > Kind regards,
> > >
> > > Michael
> > >
> > > "Omnibuzz" wrote:
> > >
> > > > It would have been good, if you had given the ddl and insert script.
> > > > Anyways, here is the answer :)
> > > >
> > > > create table tbl (ContactAddressId int, ContactId int)
> > > >
> > > > insert into tbl values(1 ,100 )
> > > > insert into tbl values(2 ,100 )
> > > > insert into tbl values(3 ,101 )
> > > > insert into tbl values(4 ,102 )
> > > > insert into tbl values(5 ,103 )
> > > > insert into tbl values(6 ,103 )
> > > > insert into tbl values(7 ,103 )
> > > > insert into tbl values(8 ,103 )
> > > > insert into tbl values(9 ,104 )
> > > >
> > > >
> > > > select a.ContactAddressId,a.contactid, count(distinct b.ContactId) from tbl
> > > > a, tbl b
> > > > where a.ContactId >= b.ContactId
> > > > group by a.ContactAddressId,a.contactid
> > > >
> > > >
> > > > Hope this helps.
> > > > --
> > > > -Omnibuzz (The SQL GC)
> > > >
> > > > http://omnibuzz-sql.blogspot.com/
> > > >
> > > >