Rolling Rowcount

Question

Rolling Rowcount

Helen T

SSC Veteran

Points: 293
More actions
October 6, 2003 at 7:26 am

#82280

Sorry in advance if this is a stupid question!
Is there a sequence type facitily in SQL server which will provide a 'rolling rowcount', or an index column in a query in the following manner:
select col1, col2, col3, @@row_counter as Index
from some_table
order by col1, col2, col3
I have searched through SQL help and the forums here but as I don't know what it would be called, I'm having a hard time finding what I want. @@Rowcount seems to be the nearest thing I can find, but I don't want the total number of rows I want the number of that particular row ---- if that makes any sense.
help!!
Helen

Helen

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Helen,

quote:
Sorry in advance if this is a stupid question!
Is there a sequence type facitily in SQL server which will provide a 'rolling rowcount', or an index column in a query in the following manner:
select col1, col2, col3, @@row_counter as Index
from some_table
order by col1, col2, col3
I have searched through SQL help and the forums here but as I don't know what it would be called, I'm having a hard time finding what I want. @@Rowcount seems to be the nearest thing I can find, but I don't want the total number of rows I want the number of that particular row ---- if that makes any sense.

several indeas are discussed here

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1

HTH

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Helen T SSC Veteran Points: 293 More actions · Answer 2

Perfect!! Thank you so much you have saved me lots more searching!!

Helen

rstone SSCertifiable Points: 6211 More actions · Answer 3

If you are not temporary table adverse...

set nocount on

select col1, col2, col3, IDENTITY(int,1,1) as [Index]

from some_table

order by col1, col2, col3

into #temp

set nocount off

select * from #temp

drop table #temp

It is easy to use local variables for a running count in an update; however, the same in a select returning a recordset chokes. If you have a table with a column for the index, you can update it with a local variable.

declare @val as int

set @val = 0

update some_table

set @val = Index = @val + 1 -- my plus sign does not show in preview - bug?

However, the order clause in this update is not permitted. If you already have a clustered index for the order required, then this method should work.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉