Post reply

AutoNumber Column

  • February 6, 2010 at 7:30 pm

    #218920

    I understand that we can generate auto number using any one of the ID column in OVER class through Row_Number. In my case, I don't have any ID column in my table,please let me know how can I generate autonumber in this case?

    Available coumn in my table: Pros_name,Pros_address, Pros_city,Pros_Pin

    Also I was told that should not use #temp table and subquries while generating auto number.

    Thanks.

  • February 6, 2010 at 9:49 pm

    #1115144

    Sounds like an interview or homework question.

    You can use ROW_NUMBER() OVER(ORDER BY...) without having to store it in a table or sub-query...

    SELECT ROW_NUMBER() OVER (ORDER BY Pros_name) AS RowNum,

    Pros_name, Pros_address, Pros_city, Pros_Pin

    FROM yourtable

    My recommendation for future questions of this nature would be... save yourself some serious time... look these up in Books Online.

    (hmmm... I'm a poet and don't know it) 😛

    --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)

  • February 7, 2010 at 2:16 pm

    #1115257

    But your feet show it ... They're long, fellow.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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