Professional Web Applications Themes

List numbers in sequential order - Microsoft SQL / MS SQL Server

Update YourTable SET YourNewCol = (SELECT COUNT(*) + 1 FROM YourTable AS b WHERE b.Field1 = YourTable.Field1) -- Andrew J. Kelly SQL Server MVP "Jane" <JaneK123.com> wrote in message news:13f801c3461f$a31070c0$a101280aphx.gbl... > A simple question, I would like to add a column of number > in sequential order based on the field value in another > column. For example: > field1 New column > ------ ---------- > a 1 > a 2 > a 3 > b 1 > b 2 > c 1 > > Thank you in advance to answer my question!...

  1. #1

    Default Re: List numbers in sequential order

    Update YourTable SET YourNewCol = (SELECT COUNT(*) + 1 FROM YourTable AS b
    WHERE b.Field1 = YourTable.Field1)


    --

    Andrew J. Kelly
    SQL Server MVP


    "Jane" <JaneK123.com> wrote in message
    news:13f801c3461f$a31070c0$a101280aphx.gbl...
    > A simple question, I would like to add a column of number
    > in sequential order based on the field value in another
    > column. For example:
    > field1 New column
    > ------ ----------
    > a 1
    > a 2
    > a 3
    > b 1
    > b 2
    > c 1
    >
    > Thank you in advance to answer my question!

    Andrew J. Kelly Guest

  2. #2

    Default list numbers in sequential order

    How can I make the new column to list number in sequential
    order based on the value from another column. In other
    words, I would like it to look like the following:
    column 1 new column
    -------- ----------
    a 1
    a 2
    a 3
    b 1
    b 2
    c 1
    Jane Guest

  3. #3

    Default Re: List numbers in sequential order

    If 'field1' is the only existing column in your table, this is impossible to
    do. As a workaround add another unique column, say an identity column & then
    you can update the new column like :

    -- add another dummy column
    ALTER TABLE tbl ADD idcol INT IDENTITY(1,1)
    GO
    -- update the new column
    UPDATE tbl
    SET newcol = (SELECT COUNT(*)
    FROM tbl t1
    WHERE t1.existingcol = tbl.existingcol
    AND t1.idcol <= tbl.idcol) ;

    GO
    -- Now remove the identity column
    ALTER TABLE source DROP COLUMN idcol
    GO

    As a mandatory practice, always make sure you identify the column/set of
    columns which can uniquely identify a row in your table & explicitly declare
    them as the primary key.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  4. #4

    Default Re: list numbers in sequential order

    See response to your previous post.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  5. #5

    Default Re: List numbers in sequential order

    "Anith Sen" wrote in message
    > If 'field1' is the only existing column in your table, this is impossible
    to
    > do....
    *Impossible*?....we may have to get to P.Diddy again :~)

    See the thread:T-SQL Incrementing values
    especially the posts by Nayan Raval.
    [url]http://tinyurl.com/gfk5[/url]

    Of course you can do all this ranking stuff without
    using those obsolete/leaden correlated subqueries in RAC :~).

    RAC v2.2 and QALite released.
    [url]www.rac4sql.net[/url]



    Groucho Guest

  6. #6

    Default Re: List numbers in sequential order

    Obviously that is not a 'single column' solution. The column rc is faked &
    it needed a Number table to boot. A different and nice approach though :-)

    As far as the Oracle/DB2 solutions are concerned, they are clearly a
    violation of physical data independence, just like the identity column
    make-shift!

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  7. #7

    Default Re: list numbers in sequential order

    Jane,

    If you just have to do this once on a large table, this is a
    quick and dirty solution that will probably work...
    It's undoented and not for production code:

    CREATE TABLE T (
    [ID] char(1) NOT NULL
    )

    insert into T
    select left(CustomerID,1)
    from Northwind..Customers
    go

    alter table T add subID int
    create clustered index T_ci on T(ID)
    go

    declare c int
    declare id char(1)
    set c = 0

    update T set
    c = subID = case when id = id then c + 1 else 1 end,
    id = id

    go
    select * from T order by ID, subID
    go
    drop table T

    Steve Kass
    Drew University

    Jane wrote:
    >How can I make the new column to list number in sequential
    >order based on the value from another column. In other
    >words, I would like it to look like the following:
    > column 1 new column
    > -------- ----------
    > a 1
    > a 2
    > a 3
    > b 1
    > b 2
    > c 1
    >
    >
    Steve Kass Guest

  8. #8

    Default Re: List numbers in sequential order

    "Anith Sen" wrote in message
    > As far as the Oracle/DB2 solutions are concerned, they are clearly a
    > violation of physical data independence, just like the identity column
    > make-shift!
    Does anybody really care?Besides that question is mute, just ask
    the sql99 standards committee.Rendering any correlated subquery
    irrelevant is a good thing:).Expediency trumps theory.Ask any vendor
    or anyone posting questions to this newsgroup.
    Sql is being made a mile wide and an inch deep.
    Will be interesting to see how wide (and deep) MS makesYukon.
    A 'big' behind (especially in the enterprise dbs market) is a bad thing:~).



    Groucho Guest

  9. #9

    Default Re: List numbers in sequential order

    >> Does anybody really care? Besides that question is mute, just ask the
    sql99 standards committee. <<

    Apparently yes... at least those who think SQL based products are a success
    only due to its basis (at least partially) on relational principles. As far
    the committee goes, what will they do if they don't beat the drum around the
    vendors :-)
    >> Expediency trumps theory. Ask any vendor or anyone posting questions to
    this newsgroup. <<

    Obviously, it seems like all of us enjoy patching up the flaw in the
    schema/logical model with a temporary workaround and place the blame on the
    "requirements of the real world"!
    >> A 'big' behind (especially in the enterprise dbs market) is a bad
    thing:~). <<

    But a well-rounded one is not :-)

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  10. #10

    Default Re: List numbers in sequential order

    "Anith Sen" wrote in message
    > Obviously, it seems like all of us enjoy patching up the flaw in the
    > schema/logical model with a temporary workaround and place the blame on
    > the "requirements of the real world"!
    And what about all those that enjoy patching up the flaws on the
    "requirements of the real world" with a temporary workaround and place
    the blame on the schema/logical model!
    > >> A 'big' behind (especially in the enterprise dbs market) is a bad
    > thing:~). <<
    > But a well-rounded one is not :-)
    Touche! :~)





    Groucho Guest

Similar Threads

  1. ORDER BY (numbers and letters)
    By Glen T in forum Coldfusion Database Access
    Replies: 18
    Last Post: October 10th, 05:59 PM
  2. Auto generate unique order number sequential
    By DutchCur in forum Macromedia ColdFusion
    Replies: 1
    Last Post: February 17th, 01:34 PM
  3. getting a list of random numbers
    By Thomas L in forum PHP Development
    Replies: 3
    Last Post: January 3rd, 01:33 PM
  4. Create Sequential Numbers
    By Steven Rudolph in forum FileMaker
    Replies: 1
    Last Post: September 25th, 10:45 AM
  5. sql SORT order not working on numbers?
    By Alpay Eno in forum ASP Database
    Replies: 2
    Last Post: July 10th, 03:47 PM

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