How can I assign a sequential integer to a SELECT column?

  • IowaDave

    SSCommitted

    Points: 1640

    I want to do this:

    SELECT Num = 1,

    Name

    FROM Employees

    :

    :

    where Num is 1,2,3,4,etc. on each row result.

    In SQL 2005, I think I can use ROW_COUNTER. Is there a way to do this in SQL 2000?

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    Assuming you had a way to order the table to set up the sequential number, yes - but that method is worse than the temp table method. Meaning - use a correlated sub-query and count all of the rows before this one in the ordering you pick.

    Pretty much everything else I know of involves the table itself or a temp table.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719984

    As Matt mentioned, there isn't a good way to do this in SQL 2000. There are some complicated queries, but they often don't perform well. A temp table can work well, depending on your scale.

    Often, however, this is a client side issue. You run through the results, track the record you've hit.

    Also, please be careful where you post. Each forum has it's name in the upper left from here. Choose the one for your version.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply