Get row index in SQL Server 2000

Posted on 2009-12-23
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
    LVL 41

    Accepted Solution

    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

    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

    LVL 5

    Expert Comment

    You can do this directly in SQL2000, as per Microsoft's page:;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:
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>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()

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now