create table #t( f1 int primary key, c char(3))

insert #t values(1, 'abc')
insert #t values(11, 'abc')
insert #t values(111, 'abc')
insert #t values(23, 'cba')
insert #t values(234, 'zze')
insert #t values(2345, 'zze')
insert #t values(345, 'yyw')

select * from #t

-- Method #1: Using a self-join & GROUP BY query
select t1.f1, t1.c, count(*) AS uniq
from #t t1 
join #t t2
on t2.f1 <= t1.f1
group by t1.f1 , t1.c
order by 3

-- Method #2: Using a correlated query
select t2.f1, t2.c,
        (select count(*) from #t t1 where t1.f1 <= t2.f1) AS uniq
from #t t2
order by 3

This page was last updated on May 01, 2006 04:28 PM.