Generating Sequence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generating Sequence

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-03-15 : 12:14:06

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-15 : 12:38:17

Yes. Use the ROW_NUMBER() function.

E 12°55'05.25"
N 56°04'39.16"

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2008-03-15 : 12:42:47

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-15 : 14:08:51

Yes. Use the ROW_NUMBER() function.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-15 : 14:26:17

For that, use DENSE_RANK() function.

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

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources