Post reply

Generating sequence

  • March 15, 2008 at 10:13 am

    #379237

    Guys,

    I have to generate sequence for distinct group of values for example

    intially seq is set to 1 through out the table

    categorydescidseq

    __________________________________

    AccountingAccounting61

    AccountingAccounting72

    AccountingFinal 8 1

    AccountingFinal 9 2

    AddendumAddendum 10 1

    Is there any way to accomplish this?

    Any suggestions and inputs would help

    Thanks

  • March 15, 2008 at 10:21 am

    #790023

    Are you storing the sequence number, or is this for returning the data?

    For querying the data you could use the row_number() function.

    Select

    ROW_NUMBER() Over (Partition By Category, Description ORder By id) as seq,

    *

    From

    table

    For inserting into the table you would need to do something like:

    Insert Into table

    Select

    value list,

    Max(A.seq) + 1

    From

    table A Join

    table B On

    A.category = B.category And

    A.description = B.description

    Where

    A.category = ? And

    B.description = ?

    Group By

    A.category,

    A.description

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 15, 2008 at 10:32 am

    #790027

    Jack beat me to it but I'll post my solution anyway... the reason is that I want you to see an example of how to post information about a table and the data. If you take the time to do this on posts, you're likely to get absolutely correct answers the first time and a lot faster (although Jack is fast as hell on these 😀 )... to make the data is easy... click on the URL in my signature to see how.

    --===== Create and populate a test table to demo the problem with

    -- THIS IS NOT PART OF THE SOLUTION

    CREATE TABLE #yourtable

    (Category VARCHAR(20),

    [Desc] VARCHAR(20),

    ID INT)

    INSERT INTO #yourtable

    (Category,[Desc],ID)

    SELECT 'Accounting','Accounting','6' UNION ALL

    SELECT 'Accounting','Accounting','7' UNION ALL

    SELECT 'Accounting','Final','8' UNION ALL

    SELECT 'Accounting','Final','9' UNION ALL

    SELECT 'Addendum','Addendum','10'

    --===== Solve the problem is 2k5

    SELECT Category,[Desc],ID,

    ROW_NUMBER() OVER (PARTITION BY Category,[Desc] ORDER BY Category,[Desc],ID) AS Seq

    FROM #yourtable

    ORDER BY Category,[Desc],ID

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

  • March 15, 2008 at 10:34 am

    #790028

    Oh yeah... almost forgot... I recommend that you don't actually use keywords for column names...

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

  • March 15, 2008 at 10:35 am

    #790029

    Thanks for the response, I didnt put the right way that I wanted I need something like below example

    category desc id seq

    __________________________________

    Accounting Accounting 6 1

    Accounting Accounting 7 1

    Accounting Final 8 2

    Accounting Final 9 2

    Addendum Addendum 10 3

    Is this possible

    Any suggestions and inputs would help

    Thanks

  • March 15, 2008 at 10:41 am

    #790031

    Jeff, thanks for the response I didnt put the right way that I wanted I need something like below example

    category desc id seq

    __________________________________

    Accounting Accounting 6 1

    Accounting Accounting 7 1

    Accounting Final 8 2

    Accounting Final 9 2

    Addendum Addendum 10 3

    Is this possible

    Any suggestions and inputs would help

  • March 15, 2008 at 11:06 am

    #790039

    Sure... use DENSE_RANK instead of ROW_NUMBER... look it up in Books Online... except for the partition, the rest of the query will be pretty much the same...

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

  • March 15, 2008 at 11:55 am

    #790045

    Like this...

    --===== Solve the problem is 2k5

    SELECT Category,[Desc],ID,

    DENSE_RANK() OVER (ORDER BY Category,[Desc]) AS Seq

    FROM #yourtable

    ORDER BY Category,[Desc],ID

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

Viewing 8 posts - 1 through 7 (of 7 total)

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