1

Possible Duplicate:
SQL Server ROW_NUMBER() on SQL Server 2000?

How do I return a row and an ascending counter as a result set?

The function Row_number() is not known in MS SQL 2000.

Community
  • 1
  • 1
Phillip
  • 33
  • 2

1 Answers1

2

Create a variable table with an identity column and insert the results into it:

DECLARE TABLE @MYTABLE
(
    ID INT IDENTITY,
    VALUES......
)
INSERT INTO @MYTABLE
SELECT MYVALUES.....

SELECT * FROM @MYTABLE
SQLMason
  • 3,275
  • 1
  • 30
  • 40
  • Thank you. I was trying to avoid a middle man table. But your answer is what I was using. I guess I'll go with that. Thanks again. – Phillip May 20 '11 at 12:51
  • @Phillip: Sorry, SQL 2000 just don't play nice. We use SQL 2005 and set its compat mode to 2000... eh. – SQLMason May 20 '11 at 13:29
  • 1
    Unfortunately, this **is not** guaranteed to give consective numbers. See demo here http://stackoverflow.com/q/4219201/27535 and then see alternatives here http://stackoverflow.com/q/4081753/27535 – gbn May 20 '11 at 13:30
  • 1
    @gbn: Thanks for pointing that out. The problem is different than what the question asks though. If he's using that just for displaying purposes, then there isn't a problem. But if he's using it for paging, then this wouldn't work well at all. – SQLMason May 20 '11 at 13:42
  • Agree, it's still useful depending on how OP wants to use it. Just wanted to add this warning – gbn May 20 '11 at 13:44