Creating Row Number Field

Question

Post reply

Creating Row Number Field

Beratung

SSC Eights!

Points: 940
More actions
September 13, 2006 at 11:25 am

#114336

I'd like to do a select off of a table and add a field that assigns a row number in the results. I assume I need to use a cursor and temp table. Can anyone give me an example. I am a novice programmer at best.
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 1

--try

select fld1, fld2, identity(int, 1,1) as RowNumber

into dbo.Tbl_Result

from dbo.Tbl_Source

order by fld3 --or some other criteria for ordering or other means of getting the result data set

bwalker SSC Veteran Points: 296 More actions · Answer 2

The suggested method is not guaranteed to work, as discussed in this KB article...

http://support.microsoft.com/default.aspx?scid=kb;en-us;273586

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 3

in 3 yrs I never saw this method fail on integer IDs.

Can u offer an alternative?

bwalker SSC Veteran Points: 296 More actions · Answer 4

This article caused the KB article to be revised...

http://www.sqlmag.com/articles/index.cfm?articleid=44138&puuid=ACE4B7F7-1279-9119-120E9223A3A9D05A

This article is about the same topic...

http://www.sqlmag.com/Article/ArticleID/43553/sql_server_43553.html

The most commonly used alternative is probably a temporary table containing a column with the IDENTITY property, populating it with INSERT ... SELECT ... ORDER BY ...

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Tried to reproduce the error described in that article - no luck.

Everytime it inserts lines in right order.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

I've never seen it fail either (and it's been 10 years)... however, the first article says it's guaranteed to be OK if you create the table with the IDENTITY column first, and then do an insert instead of using SELECT/INTO.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)