hi

i am using SQL server 2000. I want to know the record position while
retrieving records. its like row_number() in sql server 2005

Is it possible?

Here is one way to emulate ROW_NUMBER in SQL Server 2000, but be aware this
can be very slow with large data set:

CREATE TABLE Foo (
keycol CHAR(2) PRIMARY KEY,
datacol CHAR(1))

INSERT INTO Foo VALUES ('AA', 'a')
INSERT INTO Foo VALUES ('AC', 'c')
INSERT INTO Foo VALUES ('BC', 'd')
INSERT INTO Foo VALUES ('BA', 'e')

SELECT keycol,
datacol,
(SELECT COUNT(*)
FROM Foo AS B
WHERE B.keycol <= A.keycol) AS rn
FROM Foo AS A
ORDER BY keycol

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Tue, 25 Mar 2008 05:24:49 -0700 (PDT), shilpa.vastrad@gmail.com
wrote:

There rarely is a good reason for needing a row number. What is yours?
-Tom.


>hi
>
>i am using SQL server 2000. I want to know the record position while
>retrieving records. its like row_number() in sql server 2005
>
>Is it possible?

(shilpa.vastrad@gmail.com) writes:
> i am using SQL server 2000. I want to know the record position while
> retrieving records. its like row_number() in sql server 2005
>
> Is it possible?

As Plamen mentioned, the direct method to mimick the row_number()
function in SQL 2000 with a COUNT subquery performs poorly on large
datasets.

The alternative is to bounce data over a temp table with an IDENTITY
column:

CREATE TABLE #t (ident int IDENTITY, ....)
INSERT #t (...)
SELECT ...
ORDER BY <what you want to number on>

It's important that you use CREATE TABLE to create the table, as with
SELECT INTO there is no guarantee that the IDENTITY column will obey
the ORDER BY. It's not really clear whether this is the case for INSERT
either, but at least the odds seems to be better!

If you need the corresponding to PARTITION BY, you need an extra column
that you set like:

UPDATE #t
SET newnum = a.ident - b.minident + 1
FROM #t a
JOIN (SELECT col, minident = MIN(ident) FROM #t GROUP BY col) AS b
ON a.col = b.col



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Tom van Stiphout (no.spam.tom7744@cox.net) writes:
> There rarely is a good reason for needing a row number. What is yours?

The row_number() function is one of the absolutely most useful additions
to SQL 2005. The only thing that can compete is the new error handling.
There are so many problems which are solved easily and efficiently with
the row_number function.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mar 26, 3:47 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Tom van Stiphout (no.spam.tom7...@cox.net) writes:
>
> > There rarely is a good reason for needing a row number. What is yours?
>
> The row_number() function is one of the absolutely most useful additions
> to SQL 2005. The only thing that can compete is the new error handling.
> There are so many problems which are solved easily and efficiently with
> the row_number function.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

thanks to all.
the solutions are pretty good. But not feasible to me. is any other
solution available. If so then plz

(shilpa.vastrad@gmail.com) writes:
> thanks to all.
> the solutions are pretty good. But not feasible to me. is any other
> solution available. If so then plz

If they are not feasible to you, it would be a good thing if you could
provide more details the problem you are trying to solved, and why the
suggested solutions will not work for you.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

shilpa.vastrad@gmail.com wrote:

> On Mar 26, 3:47 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
>> Tom van Stiphout (no.spam.tom7...@cox.net) writes:
>>
>>> There rarely is a good reason for needing a row number. What is yours?
>> The row_number() function is one of the absolutely most useful additions
>> to SQL 2005. The only thing that can compete is the new error handling.
>> There are so many problems which are solved easily and efficiently with
>> the row_number function.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>>
>> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> thanks to all.
> the solutions are pretty good. But not feasible to me. is any other
> solution available. If so then plz

Why aren't they feasible to you? Can you give an example with a small
amount of sample data, and explain in conceptual terms what you would
want to do with that sample data?