Professional Web Applications Themes

generating incrementing number in column on select - Microsoft SQL / MS SQL Server

Hello all - I'm trying to populate a column during a select statement such that the first row in the answer set will have a value of '1' and each subsequent row in the answer set will have a value of the preceding row's value + '1'. (ie., 1,2,3,4,5,...N) I'm coming from the Teradata world and my standard query to do this cannot work because apparently there isn't a 'ZEROIFNULL' command in TRANSACT-SQL. Does anyone have a routine on how to do this they could post? It will save me some research time. Also, is there a way without using ...

  1. #1

    Default generating incrementing number in column on select

    Hello all -

    I'm trying to populate a column during a select statement
    such that the first row in the answer set will have a
    value of '1' and each subsequent row in the answer set
    will have a value of the preceding row's value + '1'.

    (ie., 1,2,3,4,5,...N)

    I'm coming from the Teradata world and my standard query
    to do this cannot work because apparently there isn't
    a 'ZEROIFNULL' command in TRANSACT-SQL.

    Does anyone have a routine on how to do this they could
    post? It will save me some research time.

    Also, is there a way without using the EM GUI to get back
    the DDL/CREATE TABLE statement for tables that already
    exist in databases? In Teradata we use 'show table
    <tablename>', but here again, apparently there is
    no 'show table' command in TRANSACT-SQL.

    Thanks in advance.



    randyv Guest

  2. #2

    Default Re: generating incrementing number in column on select

    Randy,

    Welcome! Regarding the query problem, see if the below article is of help:

    HOW TO: Dynamically Number Rows in a Select Statement
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q186133

    If not, can you please post back the table DDL so that we can work on it?For
    generating table DDL, there are limited options, querywise, without using
    EM.You can use sp_help <tablename> to get back the table structure details ,
    though not the 'CREATE TABLE...' command itself. The limited options I was
    talking about are the SQL-DMO and a utility.I have listed more details here:

    http://www.tkdinesh.com/faq/ans/schscript.html

    To be complete, Query yzer with SQL2000 has object explorer(press F8)
    where you can traverse down to the required table, right click and get the
    table DDL.This is again GUI, but I just wanted to let you know.


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "randyv" <com> wrote in message
    news:03b101c34d62$6a2acef0$gbl... 


    Dinesh.T.K Guest

  3. #3

    Default Re: generating incrementing number in column on select

    > I'm trying to populate a column during a select statement 

    http://www.aspfaq.com/2427


    Aaron Guest

Similar Threads

  1. RAND() generating same number in rapid sequence
    By Jim Michaels in forum MySQL
    Replies: 2
    Last Post: April 25th, 09:02 PM
  2. Replies: 4
    Last Post: January 28th, 12:26 PM
  3. Incrementing Number in Repeat Region - Help!
    By Tom in forum PHP Development
    Replies: 2
    Last Post: August 25th, 03:45 AM
  4. Replies: 0
    Last Post: July 9th, 06:28 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139