Numbering rows as a column

Question

Numbering rows as a column

ibbly

SSC Rookie

Points: 29
More actions
August 9, 2003 at 5:38 am

#101614

Please excuse my lack of knowledge, but I'm trying to get the equivalent of a row number as part of my recordset.
For example, if the data is:
4, 100
9, 200
11,300
17,100
18,200
where the first column is just arbitrary for this purpose, i need to number these rows as part of a rowset.
Any help to this problem is greatly appreciated.
Thank you.

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

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

There's no Oracle-equivalent ROWNUM in SQL Server unfortunately. However, either of the follow methods can be used if you have column/s that uniquely identify each row.

If no unique key columns then you might have to just use the latter method:

 

use pubs



select 

    RowNum = (select count(*) from authors [a1] where a1.au_id <= a2.au_id)

    ,* from authors [a2]

    order by au_id



select 

    RowNum = IDENTITY(INT,1,1)

    ,* INTO #authors from authors

    order by au_id

select* from #authors

Cheers,

- Mark

Cheers,
- Mark

vivian123 Old Hand Points: 390 More actions · Answer 2

This is in reply to Mark's post.

You could also use a variable of type TABLE instead of a temporary table.

I wonder which of the 2 methods is the more efficicent?

ibbly SSC Rookie Points: 29 More actions · Answer 3

Thanks for the replies! I will try them both. I figured it would include a temporary table, but with my limited knowledge I wasn't sure of a way to avoid that. Thanks again!