INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

How to add sequence number within groups of repeating values by NathanGriffiths
Posted: 7 Apr 04 (Edited 11 Jul 04)

The Problem

This has been asked in various forms a number of times. It occurs when you have groups of numbers in a column e.g.

SomeColumn
---------
101
101
102
102
102
102
103
104
104
104

and you want to apply a sequence number within each group of numbers, starting at 1 again for each instance of a new group, so your resulting data will look like this;


SomeColumn   Sequence
----------   --------
101          1
101          2
102          1   )
102          2   ) An ascending sequence number is created
102          3   ) for each group of numbers e.g. '102'
102          4   )

103          1
104          1
104          2
104          3


The Solution

Note: After some useful discussions on various threads I've decided to remove my original solution and point you toward the threads containing much better solutions by ESquared, Donutman, NigelRivett and others;

ESquared & Donutman's solution;
Thread183-859372

NigelRivetts solution, without requiring temp tables;
Thread183-859742

Happy coding,

Nathan

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close