Problem in Numbering rows...

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Try the following...

declare @sciid int

select @sciid = 0

while 1 = 1

begin

update city top (1) sciid = @sciid + 1 where sciid is null

if @@rowcount = 0 break

select @sciid + 1

end

MohammedU
Microsoft SQL Server MVP

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 2

select

sciid,

cityname,

rownum = when sciid = 4

then rank() over (partition sciid order by cityname)

else null

end

from city

order by cityname

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

I experimented and came up with this:

USE

tempdb;

IF EXISTS (SELECT * FROM sys.objects WHERE object_ID = OBJECT_ID(N'city') AND type IN (N'U'))

DROP TABLE city

CREATE TABLE city

(cityid INT IDENTITY(1,1),

sciid INT null);

INSERT

INTO city (sciid) values(55);

INSERT INTO CITY (sciid) values(NULL);

INSERT INTO CITY (sciid) values(55);

INSERT INTO CITY (sciid) values(NULL);

UPDATE

CITY SET sciid =c.rownum

FROM city JOIN

(SELECT c.cityid, ROW_NUMBER() OVER(PARTITION BY c.sciid ORDER BY c.cityid ) AS rownum

FROM city c

WHERE c.sciid IS NULL

) AS c

ON city.cityid = c.cityid

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt