Results 1 to 2 of 2

Thread: Assign SEQUENCE

  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Exclamation Unanswered: Assign SEQUENCE

    I want to assign the sequence for a table with 4 columns.
    col1 is identity, col4 is named seq.

    now, col2 have only 4 values,
    Apple,Banana,Strawberry & Blueberry.

    col3 can have 3 vales,
    LA, SF & NY.

    Business logic:
    sequence should be first according to col1
    Banana>Blueberry>Apple>Strawberry

    then according to col2 depending on col1
    for banana col2 order = NY>LA>SF
    for Blueberry, order = LA>SF>NY
    and so on...

    So, It should look like:

    id col1 col2 seq
    102 Banana NY 0001
    147 Banana LA 0002
    145 Banana SF 0003
    234 Blueberry LA 0004
    784 Blueberry SF 0005
    547 Blueberry NY 0006
    874 Apple NY 0007
    258 Apple SF 0008
    324 Apple LA 0009
    154 Strawberry NY 0010
    671 Strawberry LA 0011
    361 Strawberry SF 0012

    Kindly write a general query for this.
    mine is not working accordingly.
    NOTE: I have thousands of rows.
    Lets put it this way

    for banana > NY say I have 100 rows
    then banana > LA (50 rows)should start from 101
    then banana > SF should start from 151.

    sat I want to sort col1>col2 in ascending order of ID.

    478 banana NY 0001
    479 banana NY 0002
    480 banana NY 0003
    so on...

    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    This seems very much like schoolwork. Please put some effort into it yourself, and show us what you've done, and we can point you in the right direction.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Tags for this Thread

Posting Permissions

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