Add row numbers for non unique values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Add row numbers for non unique values

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-05 : 02:39:26

If I have a table such ase

COL1
3333
3333
1234
1309
1309
1309
1309

is there a way to have a query number the similiar items

COL1 COL2
1,3333
2,3333
1,1234
1,1309
2,1309
3,1309
4,1309

thanks.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-05 : 04:05:11

Somthing like this


Declare @Tbl Table 
(
i int,
j int IDENTITY(1,1)
)
Insert @Tbl 
Select 3333 Union All 
Select 3333 Union All 
Select 1234 Union All 
Select 1309 Union All 
Select 1309 Union All 
Select 1309 Union All 
Select 1309


Select (Select Count(1) From @Tbl t1 Where t1.i= t.i and t1.j<=t.j   ) , i From @Tbl t

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-05 : 04:54:42

Where do you want to show data?
If you use front end application,do numbering there

Madhivanan

Failing to plan is Planning to fail

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-05 : 12:38:33

I don't really want to show the data, I want to create another table with it, the front end option does not look like it will work. I have yet to try the other.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-05 : 14:41:53

Chirag,

That works perfect Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-07 : 08:38:34

quote:
Originally posted by Vinnie881

I don't really want to show the data, I want to create another table with it, the front end option does not look like it will work. I have yet to try the other.

You can also try

Select identity(int,1,1) as row_id,* into NewTable
from oldTable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources