Counting unique FK

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!
MichaelIt 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/|||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 tb
l
> 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/
>
>|||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:
>|||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.Contacti
d)
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:
>|||Thanks for your help Omnibuzz.
Unfortunatly most of our customers haven't migrated to 2005 yet :-(
"Omnibuzz" wrote:
> If you are using SQL Server 2005, then you can use the dense_rank() functi
on,
> Much simpler..
> select a.ContactAddressId,a.contactid, dense_rank() over(order by a.Contac
tid)
> from tbl a
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Omnibuzz" wrote:
>