Counting unique FK

23 Jun 2006 6:50 AM

Michael Maes

Hi,

I'm not sure how to explain my problem, so I'll go ahead with an example.
There are two tables: tblContact & tblContactAddress
tblContactAddress has a FK ContactId.
I want my SP to return something like this:

ContactAddressId    ContactId    ...    Position
--------------------    -----------    --    ---------
1                           100            ...    1
2                           100            ...    1
3                           101            ...    2
4                           102            ...    3
5                           103            ...    4
6                           103            ...    4
7                           103            ...    4
8                           103            ...    4
9                           104            ...    5

So Field 'Position' should increase one every time FK ContactId Changes.
We're on SQL 2000

TIA!

Michael

23 Jun 2006 7:22 AM

Omnibuzz

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/

23 Jun 2006 7:53 AM

Michael Maes

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

Show quote

"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/
>
>
>

23 Jun 2006 8:27 AM

Omnibuzz

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/

Show quote

"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/
> >
> >
> >

23 Jun 2006 9:03 AM

Omnibuzz

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/

Show quote

"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/
> > >
> > >
> > >

23 Jun 2006 10:11 AM

Michael Maes

Thanks for your help Omnibuzz.
Unfortunatly most of our customers haven't migrated to 2005 yet :-(

Show quote

"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/
> > > >
> > > >
> > > >