looking replacement for row_number

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

General SQL Server Forums

New to SQL Server Programming

looking replacement for row_number

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-26 : 22:39:43

declare @a table(col1 int)
insert into @a select 1 union all select 3 union all select 6 union all select 15

require output
1,1
3,2
6,3
15,4

Prav4u
Starting Member

15 Posts

Posted - 2013-11-27 : 02:29:23

try this.

select cast(col1 as varchar) +','+ cast(ROW_NUMBER()over(order by col1) as varchar)  from @a

Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 04:22:12

why not just make the column identity and use like


declare @a table(rn int identity(1,1),col1 int)
insert into @a (col1)
select * from
(
select 1 as val union all select 3 union all select 6 union all select 15
)t
order by val

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-27 : 19:19:28

i need the function of partition of row number but then row number sort cost for huge data; was thinking any other method to create a row number from the list of number i got.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources