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
>.
>

Numbering groups of rows?

create table t (colA int, colB char(1), colC int)
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
update t set colc =
(select count(*) from t as a where t.colb >= a.colb)
yields
NULL C 12
NULL C 12
NULL C 12
NULL C 12
NULL A 4
NULL A 4
NULL A 4
NULL A 4
NULL B 8
NULL B 8
NULL B 8
NULL B 8
how can I make it yield
NULL C 3
NULL C 3
NULL C 3
NULL C 3
NULL A 1
NULL A 1
NULL A 1
NULL A 1
NULL B 2
NULL B 2
NULL B 2
NULL B 2
Thanks,
Ronupdate t set colc =
(select count(DISTINCT a.colb) from t as a where t.colb >= a.colb)
Jacco Schalkwijk
SQL Server MVP
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
> create table t (colA int, colB char(1), colC int)
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> update t set colc =
> (select count(*) from t as a where t.colb >= a.colb)
> yields
> NULL C 12
> NULL C 12
> NULL C 12
> NULL C 12
> NULL A 4
> NULL A 4
> NULL A 4
> NULL A 4
> NULL B 8
> NULL B 8
> NULL B 8
> NULL B 8
> how can I make it yield
> NULL C 3
> NULL C 3
> NULL C 3
> NULL C 3
> NULL A 1
> NULL A 1
> NULL A 1
> NULL A 1
> NULL B 2
> NULL B 2
> NULL B 2
> NULL B 2
> Thanks,
> Ron
>|||On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:

>update t set colc =
>(select count(*) from t as a where t.colb >= a.colb)
>yields
(snip)
>how can I make it yield
(snip)
Hi Ron,
Better not to store this information at all - you'll find yourself
constantly fighting to keep the rankingf column current after each
modification to the underlying data. It's better to drop the column from
the table and create a view to calculate it.
If you MUST do it in an update, try
update t set colc =
(select count(distinct a.colb) from t as a where t.colb >= a.colb)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. That worked well. But I also tried
using this in a select statement as follows for ranking
the values A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
I ask this because I am trying to understand the sql logic
to achieve these results. Hopefully, after I do enough of
these kinds of queries I will get the idea how they work.
May I ask how I could achieve the ranking from result3?
Thanks again,
Ron

>--Original Message--
> update t set colc =
>(select count(DISTINCT a.colb) from t as a where t.colb
>= a.colb)
>
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
>
>.
>|||Ron,
Try count(distinct b.colb) instead of count(distinct a.colb). I suspect
that's what you had in mind.
Steve Kass
Drew University
Ron wrote:
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql logic
>to achieve these results. Hopefully, after I do enough of
>these kinds of queries I will get the idea how they work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>
>message
>|||On Thu, 10 Feb 2005 14:21:58 -0800, Ron wrote:

>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
Hi Ron,
Try this one instead:
select a.colB, count(distinct b.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
(Note: only one letter weas changed!!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. I am also trying to figure out how to
rank A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
May I ask how I could achieve the ranking from result3?
This way, as you say, I don't really store the ranks, just
retrieve them dynamically.
Thanks again,
Ron

>--Original Message--
>On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:
>
>(snip)
>(snip)
>Hi Ron,
>Better not to store this information at all - you'll find
yourself
>constantly fighting to keep the rankingf column current
after each
>modification to the underlying data. It's better to drop
the column from
>the table and create a view to calculate it.
>If you MUST do it in an update, try
>update t set colc =
>(select count(distinct a.colb) from t as a where t.colb
>= a.colb)
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks all. I am sort of starting to get the idea. But I
wonder if I could belabor this thing one more notch:
Instead of using distinct is it possible to plant a group
by query in there? Pseudocode here:
select a.colB, count(select a.colb from a group by a.colb)
as colC from t as a inner join t as b
on a.colB >= b.colB group by a.colB
Again, I just ask because I don't really know all the
rules for t-sql, let alone the tricks. I am guessing that
t-sql does not allow Selects inside of Count(..)
Thanks again,
Ron

>--Original Message--
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql
logic
>to achieve these results. Hopefully, after I do enough
of
>these kinds of queries I will get the idea how they
work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>message
>.
>|||On Thu, 10 Feb 2005 14:45:11 -0800, Ron wrote:

>Thanks all. I am sort of starting to get the idea. But I
>wonder if I could belabor this thing one more notch:
>Instead of using distinct is it possible to plant a group
>by query in there? Pseudocode here:
>select a.colB, count(select a.colb from a group by a.colb)
>as colC from t as a inner join t as b
>on a.colB >= b.colB group by a.colB
Hi Ron,
This code won't work. I'm sure there is some way to do this with a group
by in the subquery, but it's not trivial and it'll be more complex than
the version with DISTINCT that I suggested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Instead of using distinct is it possible to plant a group by query
in there? <<
This will use a GROUP BY and get you a bit more information in the VIEW
aggregate functions. I am not sure that there is any advantage. .
CREATE TABLE Foobar (letter CHAR(1) NOT NULL);
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
CREATE VIEW FoobarReport (letter, occurs, place)
AS
SELECT F1.letter, COUNT(*),
(SELECT COUNT (DISTINCT F2.letter)
FROM Foobar AS F2
WHERE F2.letter <= F1.letter)
FROM Foobar AS F1
GROUP BY F1.letter;