(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