Count that will display the number for each record in the count increment.

[Hoardling1 via AccessMonster.com]

I am looking at counting, but showing an incremented number for each record
in a SQL Statement. For example:
This is what I have:
Column 1
12
12
12
11
11
10

My result would look like:
Column 1 Column 2
12 1
12 2
12 3
11 1
11 2
10 1

Any ideas?
Thanks

 

[Sylvain Lafontaine]

Use a subquery:

create table #t (i int primary key, c int)

insert into #t (i, c) values (1, 12)
insert into #t (i, c) values (2, 12)
insert into #t (i, c) values (3, 12)
insert into #t (i, c) values (4, 11)
insert into #t (i, c) values (5, 11)
insert into #t (i, c) values (6, 10)

select i, c, (select count (*) from #t as t2 where (t2.i <= t1.i) and t2.c =
t1.c) as column2 from #t as t1 order by i

drop table #t

 

[Hoardling1 via AccessMonster.com]

SWEET!!!!
This works perfect for what I was looking for
Thank you!!!!

Use a subquery:

create table #t (i int primary key, c int)

insert into #t (i, c) values (1, 12)
insert into #t (i, c) values (2, 12)
insert into #t (i, c) values (3, 12)
insert into #t (i, c) values (4, 11)
insert into #t (i, c) values (5, 11)
insert into #t (i, c) values (6, 10)

select i, c, (select count (*) from #t as t2 where (t2.i <= t1.i) and t2.c =
t1.c) as column2 from #t as t1 order by i

drop table #t

I am looking at counting, but showing an incremented number for each record
in a SQL Statement. For example:

[quoted text clipped - 18 lines]

Any ideas?
Thanks