Need a Set-based way of number rows

Question

Post reply

Need a Set-based way of number rows

Jeff Tilkes

Right there with Babe

Points: 747
More actions
June 6, 2003 at 7:47 am

#80350
I am looking for a way to sequentially number the rows returned from a query, I have an identity field, but because of the query criteria it will not always be sequential.
Is there a simple way (Without a loop) to number these rows?
I have already thought about creating a temp table with an identity field for the sort, inserting the records in order to the temp table to get the new numbers.
```
Create Table #tmpTable

(

Col1 varchar(50),

Col2 text,

Sort int Identity

)



INSERT INTO #tmpTable

(Col1, Col2)

SELECT Col1, Col2

FROM MyTable

WHERE Col1 = 'SomeCriteria'

ORDER BY Col3



SELECT *

FROM #tmpTable



DROP TABLE #tmpTable
```
My ultimate goal would be to have this work in a single query rather than relying on a stored procedure, but I realize that is unlikely.
Any thoughts?
Thanks

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

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

GRN Hall of Fame Points: 3260 More actions · Answer 1

if you have a unique key in the results then yes

USE PUBS

SELECT ( SELECT COUNT(*) FROM Titles T2 WHERE T1.Title_id <= T2.Title_id) As RowNo , *

FROM Titles T1

ORDER BY 1

but try this on a bigger table , and it will probably kill your server .. stick to the temporary table if you can

Lowell SSC Guru Points: 323518 More actions · Answer 2

Info you are going to use a temp teable, then the Identity() function might help you out. I've used it before and It provides the identity field for the column.

try this:

SELECT Col1,

Col2,

IDENTITY(int, 1,1) AS Sort

Into

#tmpTable

FROM MyTable

WHERE Col1 = 'SomeCriteria'

ORDER BY Col3

select * from #tmptable

lowell@stormrage.com

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Andy Warren SSC Guru Points: 119902 More actions · Answer 3

Doing the explicit create table is preferred, the select into can cause blocking in tempdb.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Jeff Tilkes Right there with Babe Points: 747 More actions · Answer 4

Thanks for the help everyone, I built the page useing my origional idea (Posted in my first message) and everything seems to be working fine.

I had not been aware of the IDENTITY() function, too bad it only works in a SELECT - INTO statement.

Thanks Again