How do I display row number with records?

  • Hi,

    I want to know the process for displaying the row numbers with records that are being displayed in a sql query.

    Are there any pseudocolumns in SQL Server 2000, like those available in Oracle (rownum etc).

    Thanks in advance.


  • There is no such thing as rownum in SQL Server. If you're after some sequential numbering this one might be interesting.

    Frank Kalis
    Microsoft SQL Server MVP
    My blog:[/url]

  • use northwind



    (select count(1) from customers cu where cu.CustomerId <= c.CustomerId)

     as Counter,

    * from customers c

    order by CustomerId

  • heber's example is really cool, but its important to realize that the counter is not a physical row number.  Frank's right, storage order in a MSSQL table is completely arbitrary, there's no such thing as a row number.  If you want to retrieve the data in a particular order, you must use the ORDER BY clause, otherwise you may get the data back in a different order the next time you run your query.


  • If you want soemthing like rownum, ad an identity column.

    Michael R. Schmidt

  • Heber!

    I've been looking for a really simple way to "add a running number" to a SELECT for a long time!  Thank YOU!  That IS a really neat trick!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

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