[Webinar] Streamline your web hosting managementRegister Today


Get row index in SQL Server 2000

Posted on 2009-12-23
Medium Priority
Last Modified: 2012-05-08

 I'm working with Transact SQL 2000 and I'm trying to do a query with the the Row Index. But, I don't have the function Row_Index() or Row_Number().

What is the equivalent of Row_Index or Row_Number in Transact SQL 2000?

Question by:Albator666
  • 2
LVL 41

Accepted Solution

ralmada earned 2000 total points
ID: 26115366
You can use an identity column in a temp table
create #table1 table (id int identity, ... yourother fields....)

insert #table1
select * from yourtable

select * from #table1

drop #table1

Open in new window

LVL 41

Expert Comment

ID: 26115415
If you want some sort of partition by, then check the following example:
Say you have the following table and you want to add a column "num" as bellow:

type	variety		num
apple	fuji		1
apple	gala		2
apple	limberwig	         3
cherry	bing		1
cherry	chelan		2
orange	navel		1
orange	valencia	         2
pear	bartlet		1
pear	bradford	         2

Run the following query:

SELECT l.type, l.variety, count(*) as num
FROM Fruits l
left join fruits r on l.type = r.type and l.variety >= r.variety
group by l.type, l.variety

Open in new window


Expert Comment

ID: 26124157
You can do this directly in SQL2000, as per Microsoft's page: http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

 select rank=count(*), a1.au_lname, a1.au_fname
   from authors a1, authors a2
   where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
   group by a1.au_lname, a1.au_fname
   order by rank

The only problem with this approach is that (As Jeff says on SQL Server Central) it's a triangular join. So, if you have ten records this will be quick, if you have a thousand records it will be slow, and with a million records it may never complete!

Another example:
Returning a Row Number in a Query: http://www.sqlteam.com/article/returning-a-row-number-in-a-query
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26151533
>>What is the equivalent of Row_Index or Row_Number in Transact SQL 2000?<<
Perhaps you should consider upgrading to at least SQL Server 2005, then you can use ROW_NUMBER()

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question