adding a counter to a select query?

I am using SQL Server 2000 v8
Is there a way to add a counter to SELECT statement without writing to
tables
Ultimately I am trying to get a dataset from a SQL statement split into
blocks of 80000 rowsSQL Server 2005 has a function
row_number()
which does just that.
There was a thread somewhere about doing this in 2000 but the solution
looked quite complex.
"KayC" wrote:

> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||How do I page through a recordset?
http://www.aspfaq.com/show.asp?id=2120
How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"KayC" wrote:

> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Do a search in this forum on "Rank" and you will find several examples.
This article also has some pointers...
http://www.aspfaq.com/show.asp?id=2427
Also check out this article on paging...
http://www.aspfaq.com/show.asp?id=2120
However, I dont think either solution is intended for that volume of data.
You will have to see how they perform.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||When I need a counter, I create a temp or variable table.
CREATE TABLE #mytemp ( entryid int IDENTITY (1,1), UserName varchar(12)
Insert into #mytemp (UserName)
Select UserName from dbo.Users order by UserName
Select entryid, UserName from #mytemp
DROP TABLE #mytemp
That gives me a counter.
2005 has the feature. the above is for 2000 pretty much.
Be careful, you don't want to do this for 1,000,000 rows. Use some common
sense.
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1148402218.481826.115810@.i40g2000cwc.googlegroups.com...
> I am using SQL Server 2000 v8
> Is there a way to add a counter to SELECT statement without writing to
> tables
> Ultimately I am trying to get a dataset from a SQL statement split into
> blocks of 80000 rows
>|||Thanks guys for pointing me in the right direction
I have managed to solve the problem by creating a table variable using
the entryid sloan suggested
Cheers!
Kay