Generating sequence

Question

Post reply

Generating sequence

am-244616

SSCarpal Tunnel

Points: 4936
More actions
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

category desc id seq

__________________________________

Accounting Accounting 6 1

Accounting Accounting 7 2

Accounting Final 8 1

Accounting Final 9 2

Addendum Addendum 10 1

Is there any way to accomplish this?

Any suggestions and inputs would help

Thanks

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

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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)
Intro to Tally Tables and Functions

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 4

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

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

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)
Intro to Tally Tables and Functions