numbering rows of unordered table?

create table t (colA int, colB char(1), colC int)
insert into t(colB, colC) Values('C', 3)
insert into t(colB, colC) Values('C', 1)
insert into t(colB, colC) Values('C', 4)
insert into t(colB, colC) Values('C', 2)
insert into t(colB, colC) Values('A', 4)
insert into t(colB, colC) Values('A', 1)
insert into t(colB, colC) Values('A', 3)
insert into t(colB, colC) Values('A', 2)
insert into t(colB, colC) Values('B', 2)
insert into t(colB, colC) Values('B', 3)
insert into t(colB, colC) Values('B', 4)
insert into t(colB, colC) Values('B', 1)
so colA of table t contains all nulls right now.
Select * from t
NULL C 3
NULL C 1
NULL C 4
NULL C 2
NULL A 4
NULL A 1
NULL A 3
NULL A 2
NULL B 2
NULL B 3
NULL B 4
NULL B 1
I need to number each row of table t so it looks like this
Select * from t Order By colB, colC
1 A 1
2 A 2
3 A 3
4 A 4
5 B 1
6 B 2
7 B 3
8 B 4
9 C 1
10 C 2
11 C 3
12 C 4
In my actual app Table t already exists with colA = null
and colB and colC as above and thousands of rows. Thus,
to say
Update t set colA = 1 Where colB = 'A' And colC = '1'
Update t set colA = 2 Where colB = 'A' And colC = '2'
Update t set colA = 3 Where colB = 'A' And colC = '3'
...
Update t set colA = 9 Where colB = 'C' And colC = '1'
Update t set colA = 10 Where colB = 'C' And colC = '2'
...
is clearly is not the way to go. I humbly request if
someone could show me how to number the rows with T-sql
the correct way. My problem is that I don't know how to
increment the seed number and how to apply it to the
desired order. I am thinking a while loop, but what flag
to use to stop the loop? How to order the rows?
Thanks,
RonTry,
select
count(*) as colA,
a.colB,
a.colC
from
t as a
inner join
t as b
on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
group by
a.colB,
a.colC
order by
1
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Ron" wrote:

> create table t (colA int, colB char(1), colC int)
> insert into t(colB, colC) Values('C', 3)
> insert into t(colB, colC) Values('C', 1)
> insert into t(colB, colC) Values('C', 4)
> insert into t(colB, colC) Values('C', 2)
> insert into t(colB, colC) Values('A', 4)
> insert into t(colB, colC) Values('A', 1)
> insert into t(colB, colC) Values('A', 3)
> insert into t(colB, colC) Values('A', 2)
> insert into t(colB, colC) Values('B', 2)
> insert into t(colB, colC) Values('B', 3)
> insert into t(colB, colC) Values('B', 4)
> insert into t(colB, colC) Values('B', 1)
> so colA of table t contains all nulls right now.
> Select * from t
> NULL C 3
> NULL C 1
> NULL C 4
> NULL C 2
> NULL A 4
> NULL A 1
> NULL A 3
> NULL A 2
> NULL B 2
> NULL B 3
> NULL B 4
> NULL B 1
> I need to number each row of table t so it looks like this
> Select * from t Order By colB, colC
> 1 A 1
> 2 A 2
> 3 A 3
> 4 A 4
> 5 B 1
> 6 B 2
> 7 B 3
> 8 B 4
> 9 C 1
> 10 C 2
> 11 C 3
> 12 C 4
> In my actual app Table t already exists with colA = null
> and colB and colC as above and thousands of rows. Thus,
> to say
> Update t set colA = 1 Where colB = 'A' And colC = '1'
> Update t set colA = 2 Where colB = 'A' And colC = '2'
> Update t set colA = 3 Where colB = 'A' And colC = '3'
> ...
> Update t set colA = 9 Where colB = 'C' And colC = '1'
> Update t set colA = 10 Where colB = 'C' And colC = '2'
> ...
> is clearly is not the way to go. I humbly request if
> someone could show me how to number the rows with T-sql
> the correct way. My problem is that I don't know how to
> increment the seed number and how to apply it to the
> desired order. I am thinking a while loop, but what flag
> to use to stop the loop? How to order the rows?
> Thanks,
> Ron
>|||here is the update.
update
t
set
colA = (select count(*) from t as a where t.colB + ltrim(t.colC) >= a.colB
+ ltrim(a.colC))
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> count(*) as colA,
> a.colB,
> a.colC
> from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
> group by
> a.colB,
> a.colC
> order by
> 1
> go
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
>
> "Ron" wrote:
>|||Thanks very much for your reply. I guess the trick was in
the self join. I took this one step further and performed
an update (as I need to hardcode these numbers):
update t set t.colA = t2.colA
From t Join
(select count(*) as colA, a.colB, a.colC from t as a inner
join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
(b.colC) group by a.colB, a.colC) t2
on t.colB = t2.colB and t.colC = t2.colC
Question: someone advised me that using joins in an
update statement is not correct. But this Update
statement accomplished what I needed. Any comments
appreciated.
Thanks again for your help.
Ron

>--Original Message--
>Try,
>select
> count(*) as colA,
> a.colB,
> a.colC
>from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
>group by
> a.colB,
> a.colC
>order by
> 1
>go
>How to dynamically number rows in a SELECT Statement
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;186133
>
>AMB|||Read my last post.
AMB
"Ron" wrote:

> Thanks very much for your reply. I guess the trick was in
> the self join. I took this one step further and performed
> an update (as I need to hardcode these numbers):
> update t set t.colA = t2.colA
> From t Join
> (select count(*) as colA, a.colB, a.colC from t as a inner
> join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
> (b.colC) group by a.colB, a.colC) t2
> on t.colB = t2.colB and t.colC = t2.colC
> Question: someone advised me that using joins in an
> update statement is not correct. But this Update
> statement accomplished what I needed. Any comments
> appreciated.
> Thanks again for your help.
> Ron
>
> us;186133
>|||Thanks again for this correction.

>--Original Message--
>here is the update.
>update
> t
>set
> colA = (select count(*) from t as a where t.colB +
ltrim(t.colC) >= a.colB
>+ ltrim(a.colC))
>go
>
>AMB
>"Alejandro Mesa" wrote:
>
us;186133
this
null
Thus,
sql
to
flag
>.
>