Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    20

    Unanswered: Generating record ID in your select query

    If I wanted to run a query on any table and in the recordset that is returned have an 'id' field (or whatever) with the record id of that record, how would I do this?

    I'm thinking something like

    Code:
    Select field1, field2, recordNumber // derived somehow - not an actual field
    from table
    where the result woule be:
    HTML Code:
    field1   field2  1
    field1   field2  2
    field1   field2  3
    field1   field2  4
    field1   field2  5
    ...

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    the blind dude answered this,

    there is the rownumber or is it row_number function in SQL 2005

    In SQL 2000, you would have to use the IDENTITY function and populate a temp table and then select from that.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2007
    Posts
    20

    Talking

    I don't know who the blind dude is but thanks for the response.

  4. #4
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Use the ROW_NUMBER () function

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use Newid() and solve many problems at once.

    -PatP

  6. #6
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Quote Originally Posted by Pat Phelan
    I'd use Newid() and solve many problems at once.

    -PatP
    NEWID() generates a unique identifier. I think he's looking for a sequence number; not necessarily a guid.

    -A

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, Newid() generates a GUID. That's why I said it fixes many problems at once.

    I think very strongly that SKs (Surrogate Keys) ought to be "digital glue" used by an application/database for the purposes of that application/database and nothing else... I don't think that SKs should have sequence or meaning of any kind, other than to uniquely identify a row. Users shouldn't EVER see them, programmers shouldn't code their values into applications, and even DBAs should use their values only with great care.

    I have yet to see any good reason for explicitly coding the value of a surrogate key into a program. About five percent of the times I've seen the values of surrogate keys coded into scripts are Ok with me, the rest are either lazy or an invitation to a disaster to come and visit!

    -PatP

Posting Permissions

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