Number/enumerate rows in a table from scratch?

I have a table with 10 rows - one int column and 3 varchar cols. There is n
o
unique data. How can I number/enumerate the rows from say 1 to 10 with Tsq
l?
create table tbl1(
RowNum int,
fld1 varchar(5),
fld2 varchar(5),
fld3 varchar(5))
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Thanks,
RichConsider making the RowNum column an identity.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
I have a table with 10 rows - one int column and 3 varchar cols. There is
no
unique data. How can I number/enumerate the rows from say 1 to 10 with
Tsql?
create table tbl1(
RowNum int,
fld1 varchar(5),
fld2 varchar(5),
fld3 varchar(5))
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Thanks,
Rich|||Thanks. I thought about that. But I was just wondering - based on my
criteria, if it would be possible to enumerate a table with Tsql - like mayb
e
using a cursor? For example, in VBA you could use DAO code to enumerate a
table:
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
RS.Edit
RS!RowNum = i
RS.Update
i = i + 1
RS.MoveNext
Loop
This is kind of like a cursor except that a cursor seems to require
something unique. I was thinking in pseudocode Update tbl1 set top 1 Rownum
= 1. Then use a self join and set next row to max(Rownum) + 1. But how do
I
determine the next row with Tsql in my scenario?
"Tom Moreau" wrote:

> Consider making the RowNum column an identity.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
> I have a table with 10 rows - one int column and 3 varchar cols. There is
> no
> unique data. How can I number/enumerate the rows from say 1 to 10 with
> Tsql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich
>|||If this is a useful table in your system, you must remove the duplicates and
explicitly assign a primary key for data integrity purposes. For details
refer to:
http://support.microsoft.com/defaul...b;EN-US;q139444
Once you have unique rows, ranking becomes much simpler. For instance see:
http://support.microsoft.com/defaul...b;EN-US;q186133
Though it makes little sense, with you current table schema with no keys,
you can simply add the "pseudo rank" using an identity column.
Alternatively, you can use a rank like:
ALTER TABLE tbl1 ADD idCol INT NOT NULL IDENTITY
GO
SELECT ( SELECT COUNT( * )
FROM tbl1 t2
WHERE t2.fld1 = t1.fld1
AND t2.fld2 = t1.fld2
AND t2.fld3 = t1.fld3
AND t2.idCol <= t1.idCol ),
t1.fld1, t1.fld2, t1.fld3
FROM tbl1 t1 ;
GO
ALTER TABLE tbl1 DROP COLUMN idCol
GO
SELECT * FROM tbl1
Another approach is to use a table of sequentially incrementing numbers. You
can create one like :
SELECT IDENTITY( INT ) "n" INTO Nbrs FROM sysobjects s1, sysobjects s2 ;
Now you can do:
SELECT Nbrs.n, fld1, fld2, fld3
FROM ( SELECT fld1, fld2, fld3, COUNT( * )
FROM tbl1
GROUP BY fld1, fld2, fld3 ) D ( fld1, fld2, fld3, n )
INNER JOIN Nbrs
ON D.n >= Nbrs.n ;
Another way of doing this would be like:
SELECT n, fld1, fld2, fld3
FROM tbl1, Nbrs
GROUP BY fld1, fld2, fld3, n
HAVING n <= COUNT(*) ;
Anith|||Without something to provide uniqueness, you're stuck.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:57F251E0-A033-4047-9502-A9A5415814B2@.microsoft.com...
Thanks. I thought about that. But I was just wondering - based on my
criteria, if it would be possible to enumerate a table with Tsql - like
maybe
using a cursor? For example, in VBA you could use DAO code to enumerate a
table:
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
RS.Edit
RS!RowNum = i
RS.Update
i = i + 1
RS.MoveNext
Loop
This is kind of like a cursor except that a cursor seems to require
something unique. I was thinking in pseudocode Update tbl1 set top 1 Rownum
= 1. Then use a self join and set next row to max(Rownum) + 1. But how do
I
determine the next row with Tsql in my scenario?
"Tom Moreau" wrote:

> Consider making the RowNum column an identity.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
> I have a table with 10 rows - one int column and 3 varchar cols. There is
> no
> unique data. How can I number/enumerate the rows from say 1 to 10 with
> Tsql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich
>|||Thank you all for your replies and suggestions. These have really helped me
to understand about uniqueness and numbering. Actually, I sort of lost sigh
t
of why I was pursuing this, but I realized that with the VBA DAO each row in
an MS Access table, for example has a unique binary row identifier which is
now exposed for usage. But DAO uses it to movenext. So I can see that ther
e
is no way to movenext without some unique Identifier in a Sql Table.
Thanks all for your help.
Rich
"Rich" wrote:

> I have a table with 10 rows - one int column and 3 varchar cols. There is
no
> unique data. How can I number/enumerate the rows from say 1 to 10 with T
sql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich