I had a table where i needed the row number and i had forgotten to insert it. I got this solved using CTE and the Row_Number function.
Declare @test table (name varchar(50), rowno int)
insert into @test (name, rowno) Values ('pramod', 1)
insert into @test (name, rowno) Values ('dileep', 1)
insert into @test (name, rowno) Values ('hari', 1);
with r_SomeTable
as
(
select *
, row_number() over(order by name) as rnk
from @test
)
update r_SomeTable
set rowno = rnk;
select * from @test
Declare @test table (name varchar(50), rowno int)
insert into @test (name, rowno) Values ('pramod', 1)
insert into @test (name, rowno) Values ('dileep', 1)
insert into @test (name, rowno) Values ('hari', 1);
with r_SomeTable
as
(
select *
, row_number() over(order by name) as rnk
from @test
)
update r_SomeTable
set rowno = rnk;
select * from @test
No comments:
Post a Comment