Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72

    Unanswered: Ordinal Number in Results

    I would like to add a field to a query that returns an ordinal number indicating which row of the results it is. Anybuddy know how?
    eg. 1,2,3,4,5,6,7....

    Thanks,
    Carl

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To add it to a straight SQL statement, you will need to join the recordset to itself as a subset using something called a heterogeneous join, and then roll the whole thing into an aggregate query to get a count of records that can be used for your ordinal values. It's not QUITE as complicated as it sounds, and we can post an example if you want to go this way.

    The simpler method (and many times the more efficient approach) is to create your recordset in a stored procedure and insert it into a temporary table that has an incrementing identity value set. Then select from this temporary table to get your final output.

    You SHOULD be doing your input/output/analysis through stored procedures anyway...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2003
    Posts
    144
    SQL Server 2000 does not have a direct way of doing so, however version 2005 has the ROW_NUMBER() function that does the work nicely.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •