Select creating a row number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select creating a row number

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-06-26 : 13:43:26

I often need a row number printed next to the results of a select statement.

It would simplify my ASP subroutine if I could return a row number in the select statement somehow like:

SELECT xyz as ROW, * from Users where .....

such that rows begin at 1 and incriment by 1 in the result.

Anything similar to count(*) in SQL that will do what I need?

Sam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-26 : 13:53:26


select
    identity(int,1,1) as rownum,
    *
into
    #temp
from
    Users
where
    ....

select *
from
    #temp

...that's one way

<O>

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-26 : 15:57:41

And here's a link to a few
[url]http://tinyurl.com/hmh[/url]

HTH
Jasper Smith

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-26 : 22:49:20

Sam,
What about RecordSet.Recordcount?? That should work for ya.

Michael

--------------------
<Yoda>Use the Search page you must.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-06-26 : 23:32:58

No Michael he wants a row number, not a total count.

Page has a good solution. I think you should do it in your asp. Either keep a counter as you iterate through the rows. Or, and this is my suggestion, when you get your recordset, convert it to an array with GetRows(). It is much quicker to loop through that. Once you have it there, do :



if isArray(aMyRs) then
   for i = 0 to ubound(aMyRs, 2)
      response.write i + 1 & ". "
      response.write aMyRs(0, i) & "<br>"
   next
end if

Or something like that.
It will make your code run much quicker, both from an ASP perspective
as well as an SQL one as you won't need the temp table.

Hope that helps

Damian

Edited by - merkin on 06/26/2002 23:33:52

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 07:47:32

Is it possible to alter your schema by adding an identity column? You could then you report the number from the DB. No need for a temporary table to slow things down or an amendment to underlying asp code.

alter table tblYourTable add RowNum int identity(1,1) not null
go

That way the numbers are automatically generated for you.

Dan

<<monet makes money>>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources